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;