SQL Server 2016 Permissions – User Can’t See All Logins

permissionssql serversql-server-2016

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?

Best Answer

Seeing users requires the VIEW DEFINITION permission on the User Principle. This is implied by the ALTER USER permission.

With ALTER USER you can do a handy ALTER ANY USER, which is the right db_accessadmin gets. But there's no such thing for VIEW DEFINITION only.

GRANT ALTER ANY USER TO Foo           --Works
GRANT VIEW DEFINITION ANY USER TO Foo --Syntax Error

So to accomplish what you want, the easiest way would probably be to create a new Database Role, give it through a script VIEW DEFINITION rights to all users in the database. And then assign users to this new role that you want to give these rights.

IF DATABASE_PRINCIPAL_ID('OnlySeeAllUsers') IS NULL
BEGIN
    RAISERROR('OnlySeeAllUsers role is not defined yet, creating it', 1, 1) WITH NOWAIT
    CREATE ROLE OnlySeeAllUsers
END

DECLARE @QUERY NVARCHAR(MAX);
DECLARE @UserName NVARCHAR(256);
DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;

DECLARE userIterator CURSOR LOCAL FAST_FORWARD
FOR
    SELECT [DAT].[name] FROM sys.database_principals AS DAT
    WHERE ([DAT].[TYPE] = 'U' --Windows logins
    OR [DAT].[TYPE] = 'S') --SQL Logins
    AND [DAT].[SID] IS NOT NULL --Filters out sys/information schema, 
                                --which will cause errors otherwise
OPEN userIterator;
FETCH NEXT FROM userIterator INTO @UserName;    
WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        SET @QUERY = N'GRANT VIEW DEFINITION ON USER::' + @UserName + ' TO OnlySeeAllUsers';
        EXEC (@QUERY);
        FETCH NEXT FROM userIterator INTO @UserName;
    END TRY
    BEGIN CATCH
        SELECT @ErrorMessage = ERROR_MESSAGE(), 
        @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); --Get ErrorInfo

        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState) WITH NOWAIT;
        BREAK; --Abort while
    END CATCH
END    
CLOSE userIterator;
DEALLOCATE userIterator;

The above script can be saved/made into a procedure and rerun when necessary, or one can make a trigger upon creating new users to grant view definition to the database role.

Then to add a user to this database role (SQL Server 2012+)

ALTER ROLE OnlySeeAllUsers 
ADD MEMBER Foo

EXEC sp_addrolemember 'OnlySeeAllUsers', 'Foo' --Method before SQL Server 2012
Related Question