设为首页收藏本站

EPS数据狗论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 1169|回复: 0

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

[复制链接]

32

主题

313

金钱

473

积分

入门用户

发表于 2019-10-16 16:27:10 | 显示全部楼层 |阅读模式

在SQL Server数据库中如何查看一个登录名(login)的具体权限呢,如果使用SSMS的UI界面查看登录名的具体权限的话,用户数据库非常多的话,要梳理完它所有的权限,操作又耗时又麻烦,个人十分崇尚简洁、高效的方法,反感那些需要大量手工操作的UI界面操作方式,哪怕就是脚本,如果不能一次搞定,手工多操作几次(例如,切换数据库),都是不可接受的。最近遇到这个需求,就完善了一下之前的脚本get_login_rights_script.sql,输入登录名参数,将这个登录名所拥有的服务器角色、数据库角色、以及所授予具体对象的相关权限使用脚本查询出来,脚本分享如下:
  1. DECLARE @login_name    NVARCHAR(32)= 'test1';
  2. DECLARE @database_name   NVARCHAR(64);
  3. DECLARE @cmdText      NVARCHAR(MAX);
  4. IF OBJECT_ID('TempDB.dbo.#databases') IS NOT NULL
  5.   DROP TABLE dbo.#databases;
  6. CREATE TABLE #databases
  7. (
  8.   database_id    INT,
  9.   database_name  sysname
  10. );
  11. IF OBJECT_ID('tempdb.dbo.#user_db_roles') IS NOT NULL
  12.   DROP TABLE dbo.#user_db_roles;
  13. CREATE TABLE dbo.#user_db_roles
  14. (
  15.    [DB_NAME]    NVARCHAR(64)
  16.   ,[USER_NAME]  NVARCHAR(64)
  17.   ,[ROLE_NAME]  NVARCHAR(64)
  18. );
  19. IF OBJECT_ID('tempdb.dbo.#user_object_rights') IS NOT NULL
  20.   DROP TABLE dbo.#user_object_rights;
  21. CREATE TABLE dbo.#user_object_rights
  22. (  
  23.   [DATABASE_NAME]    NVARCHAR(128),
  24.   [SCHEMA_NAME]     NVARCHAR(64),
  25.   [OBJECT_NAME]     NVARCHAR(128),
  26.   [USER_NAME]      NVARCHAR(32),
  27.   [PERMISSIONS_TYPE]   CHAR(12),
  28.   [PERMISSION_NAME]   NVARCHAR(128),
  29.   [PERMISSION_STATE]   NVARCHAR(64),
  30.   [CLASS_DESC]      NVARCHAR(64),
  31.   [COLUMN_NAME]     NVARCHAR(32),
  32.   [STATE_DESC]      NVARCHAR(64),
  33.   [GRANT_STMT]      NVARCHAR(MAX),
  34.   [REVOKE_STMT]     NVARCHAR(MAX)
  35. )
  36. INSERT INTO #databases
  37. SELECT database_id ,
  38.     name
  39. FROM  sys.databases
  40. WHERE name NOT IN ('model') AND state = 0; --state_desc=ONLINE
  41. --登录名授予的服务器角色
  42. SELECT UserName    = u.name ,
  43.     ServerRole   = g.name ,
  44.     Type      = u.type,
  45.     Type_Desc    = u.Type_Desc,
  46.     Create_Date   = u.create_date,
  47.     Modify_Date   = u.modify_date,
  48.     DenyLogin    = l.denylogin
  49. FROM  sys.server_role_members m
  50.     INNER JOIN sys.server_principals g ON g.principal_id = m.role_principal_id
  51.     INNER JOIN sys.server_principals u ON u.principal_id = m.member_principal_id
  52.     INNER JOIN sys.syslogins l ON u.name = l.name
  53. WHERE l.name=@login_name
  54. ORDER BY u.name,g.name;
  55. WHILE 1= 1
  56. BEGIN
  57.   SELECT TOP 1 @database_name= database_name  
  58.   FROM #databases
  59.   ORDER BY database_id;
  60.   IF @@ROWCOUNT =0
  61.     BREAK;
  62.   SET @cmdText = N'USE ' + QUOTENAME(@database_name) + N';' +CHAR(10)
  63.   --登录名授予的数据库角色
  64.   SELECT @cmdText += N'INSERT INTO #user_db_roles
  65.             SELECT DB_NAME()   AS [DB_NAME]
  66.                 ,M.NAME    AS [USER_NAME]
  67.                 ,R.NAME    AS [ROLE_NAME]
  68.             FROM  sys.DATABASE_ROLE_MEMBERS RM
  69.                 INNER JOIN sys.DATABASE_PRINCIPALS R ON RM.ROLE_PRINCIPAL_ID = R.PRINCIPAL_ID
  70.                 INNER JOIN sys.DATABASE_PRINCIPALS M ON RM.MEMBER_PRINCIPAL_ID = M.PRINCIPAL_ID
  71.             WHERE M.NAME=@p_login_name' + CHAR(10);
  72.   EXEC SP_EXECUTESQL @cmdText, N'@p_login_name NVARCHAR(32)',@p_login_name=@login_name;
  73.   SET @cmdText = N'USE ' +QUOTENAME(@database_name) + N';' +CHAR(10);
  74.   --查看具体对象的授权问题
  75.   SELECT @cmdText +=N'INSERT INTO dbo.#user_object_rights
  76.             (  [DATABASE_NAME]   ,
  77.               [SCHEMA_NAME]    ,
  78.               [OBJECT_NAME]    ,
  79.               [USER_NAME]     ,
  80.               [PERMISSIONS_TYPE]  ,
  81.               [PERMISSION_NAME]  ,
  82.               [PERMISSION_STATE]  ,
  83.               [CLASS_DESC]     ,
  84.               [COLUMN_NAME]    ,
  85.               [STATE_DESC]     ,
  86.               [GRANT_STMT]     ,
  87.               [REVOKE_STMT]     
  88.             )
  89.             SELECT DB_NAME()           AS  [DATABASE_NAME]
  90.                , SYS.SCHEMAS.NAME       AS  [SCHEMA_NAME]
  91.                , ob.NAME            AS  [OBJECT_NAME]
  92.                , SYS.DATABASE_PRINCIPALS.NAME AS  [USER_NAME]
  93.                , dp.TYPE            AS  [PERMISSIONS_TYPE]
  94.                , dp.PERMISSION_NAME      AS  [PERMISSION_NAME]
  95.                , dp.STATE           AS  [PERMISSION_STATE]
  96.                , dp.CLASS_DESC         AS  [CLASS_DESC]
  97.                , sc.name            AS  [COLUMN_NAME]
  98.                , dp.STATE_DESC         AS  [STATE_DESC]
  99.                , dp.STATE_DESC + '' '' + dp.PERMISSION_NAME + '' ON [''+ SYS.SCHEMAS.NAME + ''].['' + ob.NAME + ''] TO ['' + SYS.DATABASE_PRINCIPALS.NAME + ''];'' COLLATE LATIN1_GENERAL_CI_AS
  100.                                AS [GRANT_STMT]
  101.                , ''REVOKE '' + dp.PERMISSION_NAME + '' ON [''+ SYS.SCHEMAS.NAME + ''].['' + ob.NAME + ''] FROM ['' + SYS.DATABASE_PRINCIPALS.NAME + ''];'' COLLATE LATIN1_GENERAL_CI_AS
  102.                                AS [REVOKE_STMT]
  103.             FROM SYS.DATABASE_PERMISSIONS dp
  104.             LEFT OUTER JOIN SYS.OBJECTS ob ON dp.MAJOR_ID = ob.OBJECT_ID
  105.             LEFT OUTER JOIN SYS.SCHEMAS ON ob.SCHEMA_ID = SYS.SCHEMAS.SCHEMA_ID
  106.             LEFT OUTER JOIN SYS.DATABASE_PRINCIPALS ON dp.GRANTEE_PRINCIPAL_ID = SYS.DATABASE_PRINCIPALS.PRINCIPAL_ID
  107.             LEFT OUTER JOIN SYS.columns sc ON ob.object_id = sc.object_id AND sc.column_id = dp.minor_id
  108.             WHERE SYS.DATABASE_PRINCIPALS.NAME =@p_login_name
  109.             ORDER BY PERMISSIONS_TYPE;'
  110.   PRINT(@cmdText);
  111.   EXEC SP_EXECUTESQL @cmdText, N'@p_login_name NVARCHAR(32)',@p_login_name=@login_name;
  112.   DELETE FROM #databases WHERE database_name=@database_name;
  113. END
  114. SELECT * FROM tempdb.dbo.#user_db_roles;
  115. SELECT * FROM dbo.#user_object_rights;
  116. IF OBJECT_ID('TempDB.dbo.#databases') IS NOT NULL
  117.   DROP TABLE dbo.#databases;
  118. IF OBJECT_ID('tempdb.dbo.#user_db_roles') IS NOT NULL
  119.   DROP TABLE dbo.#user_db_roles;
  120. IF OBJECT_ID('tempdb.dbo.#user_object_rights') IS NOT NULL
  121.   DROP TABLE dbo.#user_object_rights;
复制代码
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关闭

站长推荐上一条 /1 下一条

客服中心
关闭
在线时间:
周一~周五
8:30-17:30
QQ群:
653541906
联系电话:
010-85786021-8017
在线咨询
客服中心

意见反馈|网站地图|手机版|小黑屋|EPS数据狗论坛 ( 京ICP备09019565号-3 )   

Powered by BFIT! X3.4

© 2008-2028 BFIT Inc.

快速回复 返回顶部 返回列表