SQL Server查看login所授予的具体权限问题

--==================================================================================================================--    ScriptName      :      get_login_rights_script.sql--    Author        :      潇湘隐者  --    CreateDate      :      2015-12-18--    Description      :      查看某个登录名被授予的数据库对象的权限的脚本（授权脚本和回收权限脚本）--    Note         :      /******************************************************************************************************************    Parameters       :                  参数说明********************************************************************************************************************      @login_name     :      你要查看权限的登录名(需要输入替换的参数)********************************************************************************************************************  Modified Date  Modified User   Version         Modified Reason********************************************************************************************************************  2018-08-03    潇湘隐者     V01.00.00    新建该脚本。  2019-04-04    潇湘隐者     V01.01.00    Fix掉一个bug，某个表只允许更新某个字段，但是这里显示更新整个表。  2019-09-25    潇湘隐者     V01.02.00    解决只能查看某个用户数据库，不能查看所有数据库的权限问题。  2019-09-25    潇湘隐者     V01.03.00    解决数据库名包含中划线[-], 出现下面错误问题-------------------------------------------------------------------------------------------------------------------Msg 911, Level 16, State 1, Line 1Database '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 #databasesSELECT database_id ,    nameFROM  sys.databasesWHERE name NOT IN ('model') AND state = 0; --state_desc=ONLINE --登录名授予的服务器角色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.denyloginFROM  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.nameWHERE l.name=@login_nameORDER BY u.name,g.name;WHILE 1= 1BEGIN  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)  --登录名授予的数据库角色  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);  --查看具体对象的授权问题  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;ENDSELECT * 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;