patternsqlMinor
SQL Server User Can't See All Logins
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:
I receive a count of
I receive a count of
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?
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
This permission does not permit the user to
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.