HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

SQL Server User Can't See All Logins

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
canallsqluserloginsseeserver

Problem

I have a SQL Server 2016 instance and I've just noticed that some user logins aren't listed for some users. For example, if I execute this query:

select count(*) FROM sys.database_principals a where type = 'U'


I receive a count of 1348. But if I execute this query:

execute as user = 'some_user';
select count(*) FROM sys.database_principals a where type = 'U';
revert;


I receive a count of 92, meaning that there are 1256 users that can't be seen by the above user. If I grant the user db_accessadmin database role membership, then they're able to see all 1348 users returned from that above query. However I do not want to grant db_accessadmin to all users as that gives much more access than I want to grant.

I've compared the properties of one of the 92 users that does show up to the properties of the users that don't show up however I haven't noticed any property that looks to allow listing the user.

How may I go about allowing all users to be listed in the query to database_principals?

Solution

The minimum permission in database to make a user "see" all the users is VIEW DEFINITION.

This permission does not permit the user to alter anything, but it opens to user every definition of database objects: tables, procedures, etc.

Context

StackExchange Database Administrators Q#253888, answer score: 4

Revisions (0)

No revisions yet.