在SQL Server数据库中如何查看一个登录名(login)的具体权限呢,如果使用SSMS的UI界面查看登录名的具体权限的话,用户数据库非常多的话,要梳理完它所有的权限,操作又耗时又麻烦,个人十分崇尚简洁、高效的方法,反感那些需要大量手工操作的UI界面操作方式,哪怕就是脚本,如果不能一次搞定,手工多操作几次(例如,切换数据库),都是不可接受的。最近遇到这个需求,就完善了一下之前的脚本get_login_rights_script.,输入登录名参数,将这个登录名所拥有的服务器角色、数据库角色、以及所授予具体对象的相关权限使用脚本查询出来,脚本分享如下:

  --==================================================================================================================  --    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 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   --登录名授予的服务器角色  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)    --登录名授予的数据库角色    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;  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;

总结

以上所述是小编给大家介绍的SQL Server查看login所授予的具体权限问题,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对沃谷博客网站的支持!
如果你觉得本文对你有帮助,欢迎转载,烦请注明出处,谢谢!

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注