4

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?

2

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
  • Thank you, that was exactly what I was looking for! – Alex Nov 22 '19 at 16:49
4

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.

1

According to the documentation for sys.database_principals:

Any user can see their own user name, the system users, and the fixed database roles. To see other users, requires ALTER ANY USER, or a permission on the user. To see user-defined roles, requires ALTER ANY ROLE, or membership in the role.

You might not want to grant such a powerful permission directly, so look into other options like module signing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.