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