Let a user with minimal privileges see another user, simple with TSQL - SQL Server 2005

Published 11 March 08 05:05 PM | SQL Master 

You may be aware that by default, users that have minimal privileges cannot see other users in the sys.database_principals and sys.server_principals catalog views. This means that a user with minimal privileges that owns a table cannot see other users to whom the user might want to grant permissions. To let user user_X with minimal privileges see another user, user_Y, you can issue the following GRANT statement:

  • GRANT VIEW DEFINITION ON USER :: <user_Y> TO <user_X>

So to automate this process you can create DDL trigger as referred in BOL documentation::

CREATE TRIGGER grant_view_definition_on_principal ON DATABASE
FOR CREATE_USER, CREATE_ROLE
AS
    DECLARE @event_type sysname, @principal_name sysname, @sql nvarchar(max);
    SELECT @event_type     = eventdata().value('(/EVENT_INSTANCE/EventType) [1]','sysname');
    SELECT @principal_name = eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]','sysname');
    IF (@event_type = 'CREATE_USER')
        SELECT @sql = 'GRANT VIEW DEFINITION ON USER :: ' + @principal_name + ' TO PUBLIC ' ;
    ELSE
        SELECT @sql = 'GRANT VIEW DEFINITION ON ROLE :: ' + @principal_name + ' TO PUBLIC ' ;
    EXEC (@sql) ;
GO

Comments

# Other SQL Server Blogs around the Web said on March 11, 2008 5:50 PM:

You may be aware that by default, users that have minimal privileges cannot see other users in the sys

# SQL Server Transact-SQL (SSQA.net) : Let a user with minimal privileges see another user, simple with TSQL - SQL Server 2005 said on March 11, 2008 7:26 PM:

PingBack from http://sqlserver-qa.net/blogs/t-sql/archive/2008/03/11/3710.aspx

Anonymous comments are disabled

About SQL Master

**__________________________________** SQL Server MVP, Sr. DBA & industry expert. - Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.