SQL Server Views Specific Permissions Granted by login

Posted by satya61229 on Tue, 01 Oct 2019 01:07:21 +0200

How to check the specific rights of a login in the SQL Server database? If you use the UI interface of SSMS to check the specific rights of the login name, there are many user databases. It is time-consuming and troublesome to sort out all the rights of the user database. Individuals highly advocate concise and efficient methods and dislike those needs. A large number of manual UI interface operations, even scripts, are unacceptable if they can not be done at one time and several manual operations (for example, switching databases) are required. Recent encounters with this requirement, we improved the previous script get_login_rights_script.sql, input login parameters, and query the server role, database role of the login name, and the relevant privileges granted to specific objects using the script. The script is shared as follows:

 

--==================================================================================================================
--        ScriptName            :            get_login_rights_script.sql
--        Author                :            Xiaoxiang Hermit    
--        CreateDate            :            2015-12-18
--        Description           :            A script to view the permissions of a database object granted by a login name (authorization script and recovery permission script)
--        Note                  :            
/******************************************************************************************************************
        Parameters              :                                    Parameter description
********************************************************************************************************************
            @login_name         :            You need to view the login name of the permission (you need to enter the replacement parameters)
********************************************************************************************************************
   Modified Date    Modified User     Version                 Modified Reason
********************************************************************************************************************
    2018-08-03        Xiaoxiang Hermit V01.00.00 New script.
    2019-04-04        Xiaoxiang Hermit (V01.01.00) Fix drops a bug, and a table allows only one field to be updated, but here it shows the whole table to be updated.
    2019-09-25        Xiaoxiang Hermit V01.02.00 solves the problem that only one user database can be viewed, but not all databases can be viewed.
    2019-09-25        Xiaoxiang Hermit (V01.03.00) Solves the problem that the database name contains a middle line [-], and the following error occurs
-------------------------------------------------------------------------------------------------------------------
Msg 911, Level 16, State 1, Line 1
Database 'xxxx' does not exist. Make sure that the name is entered correctly.
-------------------------------------------------------------------------------------------------------------------
*******************************************************************************************************************/
 
DECLARE @login_name        NVARCHAR(32)= 'test1';
DECLARE @database_name     NVARCHAR(64);
DECLARE @cmdText           NVARCHAR(MAX);
 
 
IF OBJECT_ID('TempDB.dbo.#databases') IS NOT NULL
    DROP TABLE dbo.#databases;
 
CREATE TABLE #databases
(
    database_id        INT,
    database_name   sysname
);
 
IF OBJECT_ID('tempdb.dbo.#user_db_roles') IS NOT NULL 
    DROP TABLE dbo.#user_db_roles;
 
CREATE TABLE dbo.#user_db_roles
(
     [DB_NAME]        NVARCHAR(64)
    ,[USER_NAME]    NVARCHAR(64)
    ,[ROLE_NAME]    NVARCHAR(64)
);
 
 
IF OBJECT_ID('tempdb.dbo.#user_object_rights') IS NOT NULL
    DROP TABLE dbo.#user_object_rights;
 
CREATE TABLE dbo.#user_object_rights
(    
    [DATABASE_NAME]        NVARCHAR(128),
    [SCHEMA_NAME]          NVARCHAR(64),
    [OBJECT_NAME]          NVARCHAR(128),
    [USER_NAME]            NVARCHAR(32),
    [PERMISSIONS_TYPE]     CHAR(12),
    [PERMISSION_NAME]      NVARCHAR(128),
    [PERMISSION_STATE]     NVARCHAR(64),
    [CLASS_DESC]           NVARCHAR(64),
    [COLUMN_NAME]          NVARCHAR(32),
    [STATE_DESC]           NVARCHAR(64),
    [GRANT_STMT]           NVARCHAR(MAX),
    [REVOKE_STMT]          NVARCHAR(MAX)
)
 
INSERT  INTO #databases
SELECT  database_id ,
        name
FROM    sys.databases
WHERE name NOT IN ('model') AND state = 0; --state_desc=ONLINE 
 
 
--Server roles granted by login names
 
SELECT  UserName        = u.name ,
        ServerRole      = g.name ,
        Type            = u.type,
        Type_Desc       = u.Type_Desc,
        Create_Date     = u.create_date,
        Modify_Date     = u.modify_date, 
        DenyLogin       = l.denylogin
FROM    sys.server_role_members m
        INNER JOIN sys.server_principals g ON g.principal_id = m.role_principal_id
        INNER JOIN sys.server_principals u ON u.principal_id = m.member_principal_id
        INNER JOIN sys.syslogins l ON u.name = l.name
WHERE l.name=@login_name
ORDER BY u.name,g.name;
 
 
WHILE 1= 1
BEGIN
 
 
    SELECT TOP 1 @database_name= database_name   
    FROM #databases
    ORDER BY database_id;
 
    IF @@ROWCOUNT =0 
        BREAK;
 
 
    SET @cmdText =  N'USE ' + QUOTENAME(@database_name) + N';' +CHAR(10)
 
    --Database roles granted by login names
    SELECT @cmdText += N'INSERT INTO #user_db_roles
                        SELECT  DB_NAME()     AS [DB_NAME]
                               ,M.NAME        AS [USER_NAME]
                               ,R.NAME        AS [ROLE_NAME]
                        FROM    sys.DATABASE_ROLE_MEMBERS RM
                                INNER JOIN sys.DATABASE_PRINCIPALS R ON RM.ROLE_PRINCIPAL_ID = R.PRINCIPAL_ID
                                INNER JOIN sys.DATABASE_PRINCIPALS M ON RM.MEMBER_PRINCIPAL_ID = M.PRINCIPAL_ID
                        WHERE M.NAME=@p_login_name' + CHAR(10);
    
    EXEC SP_EXECUTESQL @cmdText, N'@p_login_name NVARCHAR(32)',@p_login_name=@login_name;
 
    SET @cmdText =  N'USE ' +QUOTENAME(@database_name)  + N';' +CHAR(10);
 
    --Viewing Authorization Issues for Specific Objects
    SELECT @cmdText +=N'INSERT INTO dbo.#user_object_rights
                        (    [DATABASE_NAME]     ,
                            [SCHEMA_NAME]        ,
                            [OBJECT_NAME]        ,
                            [USER_NAME]          ,
                            [PERMISSIONS_TYPE]   ,
                            [PERMISSION_NAME]    ,
                            [PERMISSION_STATE]   ,
                            [CLASS_DESC]         ,
                            [COLUMN_NAME]        ,
                            [STATE_DESC]         ,
                            [GRANT_STMT]         ,
                            [REVOKE_STMT]         
                        )
                        SELECT DB_NAME()                     AS    [DATABASE_NAME]
                             , SYS.SCHEMAS.NAME              AS    [SCHEMA_NAME]
                             , ob.NAME                       AS    [OBJECT_NAME]
                             , SYS.DATABASE_PRINCIPALS.NAME  AS    [USER_NAME]
                             , dp.TYPE                       AS    [PERMISSIONS_TYPE]
                             , dp.PERMISSION_NAME            AS    [PERMISSION_NAME]
                             , dp.STATE                      AS    [PERMISSION_STATE]
                             , dp.CLASS_DESC                 AS    [CLASS_DESC]
                             , sc.name                       AS    [COLUMN_NAME]
                             , dp.STATE_DESC                 AS    [STATE_DESC]
                             , dp.STATE_DESC + '' '' + dp.PERMISSION_NAME + '' ON [''+ SYS.SCHEMAS.NAME + ''].['' + ob.NAME + ''] TO ['' + SYS.DATABASE_PRINCIPALS.NAME + ''];'' COLLATE LATIN1_GENERAL_CI_AS 
                                                             AS  [GRANT_STMT] 
                             , ''REVOKE '' + dp.PERMISSION_NAME + '' ON [''+ SYS.SCHEMAS.NAME + ''].['' + ob.NAME + ''] FROM ['' + SYS.DATABASE_PRINCIPALS.NAME + ''];'' COLLATE LATIN1_GENERAL_CI_AS 
                                                             AS  [REVOKE_STMT]
                        FROM SYS.DATABASE_PERMISSIONS  dp
                        LEFT OUTER JOIN SYS.OBJECTS  ob ON dp.MAJOR_ID = ob.OBJECT_ID 
                        LEFT OUTER JOIN SYS.SCHEMAS ON  ob.SCHEMA_ID = SYS.SCHEMAS.SCHEMA_ID 
                        LEFT OUTER JOIN SYS.DATABASE_PRINCIPALS ON dp.GRANTEE_PRINCIPAL_ID = SYS.DATABASE_PRINCIPALS.PRINCIPAL_ID 
                        LEFT OUTER JOIN SYS.columns sc ON ob.object_id = sc.object_id AND sc.column_id = dp.minor_id
                        WHERE SYS.DATABASE_PRINCIPALS.NAME =@p_login_name
                        ORDER BY PERMISSIONS_TYPE;'
 
    PRINT(@cmdText);
    EXEC SP_EXECUTESQL @cmdText, N'@p_login_name NVARCHAR(32)',@p_login_name=@login_name;
 
    DELETE FROM #databases WHERE database_name=@database_name;
END
 
SELECT * FROM tempdb.dbo.#user_db_roles;
SELECT * FROM dbo.#user_object_rights;
 
 
IF OBJECT_ID('TempDB.dbo.#databases') IS NOT NULL
    DROP TABLE dbo.#databases;
IF OBJECT_ID('tempdb.dbo.#user_db_roles') IS NOT NULL 
    DROP TABLE dbo.#user_db_roles;
IF OBJECT_ID('tempdb.dbo.#user_object_rights') IS NOT NULL
    DROP TABLE dbo.#user_object_rights;

Topics: SQL Server Database SQL