我想在sql 2008上写一个查询，它将报告所有有权访问特定数据库的用户，或数据库中的对象，如表，视图和存储过程，直接或由于角色等。该报告将用于安全审计目的。不确定是否有人有一个查询，将完全满足我的需求，但希望能给我一个好的开始。无论是sql 2008, 2005或2000将做，我可以根据需要转换。
当前回答
CREATE PROCEDURE Get_permission
AS
DECLARE @db_name VARCHAR(200),
@sql_text VARCHAR(max)
SET @sql_text='Create table ##db_name (user_name varchar(max),'
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
OPEN db_cursor
FETCH next FROM db_cursor INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql_text=@sql_text + @db_name + ' varchar(max),'
FETCH next FROM db_cursor INTO @db_name
END
CLOSE db_cursor
SET @sql_text=@sql_text + 'Server_perm varchar(max))'
EXEC (@sql_text)
DEALLOCATE db_cursor
DECLARE @RoleName VARCHAR(50)
DECLARE @UserName VARCHAR(50)
DECLARE @CMD VARCHAR(1000)
CREATE TABLE #permission
(
user_name VARCHAR(50),
databasename VARCHAR(50),
role VARCHAR(50)
)
DECLARE longspcur CURSOR FOR
SELECT name
FROM sys.server_principals
WHERE type IN ( 'S', 'U', 'G' )
AND principal_id > 4
AND name NOT LIKE '##%'
AND name <> 'NT AUTHORITY\SYSTEM'
AND name <> 'ONDEMAND\Administrator'
AND name NOT LIKE 'steel%'
OPEN longspcur
FETCH next FROM longspcur INTO @UserName
WHILE @@FETCH_STATUS = 0
BEGIN
CREATE TABLE #userroles_kk
(
databasename VARCHAR(50),
role VARCHAR(50)
)
CREATE TABLE #rolemember_kk
(
dbrole VARCHAR(100),
membername VARCHAR(100),
membersid VARBINARY(2048)
)
SET @CMD = 'use ? truncate table #RoleMember_kk insert into #RoleMember_kk exec sp_helprolemember insert into #UserRoles_kk (DatabaseName, Role) select db_name(), dbRole from #RoleMember_kk where MemberName = ''' + @UserName + ''''
EXEC Sp_msforeachdb
@CMD
INSERT INTO #permission
SELECT @UserName 'user',
b.name,
u.role
FROM sys.sysdatabases b
LEFT OUTER JOIN #userroles_kk u
ON u.databasename = b.name --and u.Role='db_owner'
ORDER BY 1
DROP TABLE #userroles_kk;
DROP TABLE #rolemember_kk;
FETCH next FROM longspcur INTO @UserName
END
CLOSE longspcur
DEALLOCATE longspcur
TRUNCATE TABLE ##db_name
DECLARE @d1 VARCHAR(max),
@d2 VARCHAR(max),
@d3 VARCHAR(max),
@ss VARCHAR(max)
DECLARE perm_cur CURSOR FOR
SELECT *
FROM #permission
ORDER BY 2 DESC
OPEN perm_cur
FETCH next FROM perm_cur INTO @d1, @d2, @d3
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS(SELECT 1
FROM ##db_name
WHERE user_name = @d1)
BEGIN
SET @ss='insert into ##db_name(user_name) values ('''
+ @d1 + ''')'
EXEC (@ss)
SET @ss='update ##db_name set ' + @d2 + '=''' + @d3
+ ''' where user_name=''' + @d1 + ''''
EXEC (@ss)
END
ELSE
BEGIN
DECLARE @var NVARCHAR(max),
@ParmDefinition NVARCHAR(max),
@var1 NVARCHAR(max)
SET @var = N'select @var1=' + @d2
+ ' from ##db_name where USER_NAME=''' + @d1
+ '''';
SET @ParmDefinition = N'@var1 nvarchar(300) OUTPUT';
EXECUTE Sp_executesql
@var,
@ParmDefinition,
@var1=@var1 output;
SET @var1=Isnull(@var1, ' ')
SET @var= ' update ##db_name set ' + @d2 + '=''' + @var1 + ' '
+ @d3 + ''' where user_name=''' + @d1 + ''' '
EXEC (@var)
END
FETCH next FROM perm_cur INTO @d1, @d2, @d3
END
CLOSE perm_cur
DEALLOCATE perm_cur
SELECT *
FROM ##db_name
DROP TABLE ##db_name
DROP TABLE #permission
其他回答
无法评论已接受的答案，所以我将在这里添加一些评论:
I second Brad on schemas issue. From MS reference sys.objects table contains only schema-scoped objects. So to get info about "higher level" objects (i.e. schemas in our case) you need to use sys.schemas table. For [ObjectType] it's better to use obj.type_desc only for OBJECT_OR_COLUMN permission class. For all other cases use perm.[class_desc] Another type of permission which is not handled so well with this query is IMPERSONATE. To get info about impersonations one should LEFT JOIN with sys.database_principals on perm.major_id = imp.principal_id With my experience it's better to replace sys.login_token with sys.server_principals as it will show also SQL Logins, not only Windows ones One should add 'G' to allowed principal types to allow Windows groups Also, one can exclude users sys and INFORMATION_SCHEMA from resulting table, as these users are used only for service
我将发布第一个带有所有建议修复的脚本，其他部分也应该被更改:
SELECT
[UserName] = ulogin.[name],
[UserType] = CASE princ.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'G' THEN 'Windows Group'
END,
[DatabaseUserName] = princ.[name],
[Role] = null,
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.type_desc -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END,
[ObjectName] = CASE perm.[class]
WHEN 1 THEN OBJECT_NAME(perm.major_id) -- General objects
WHEN 3 THEN schem.[name] -- Schemas
WHEN 4 THEN imp.[name] -- Impersonations
END,
[ColumnName] = col.[name]
FROM
--database user
sys.database_principals princ
LEFT JOIN
--Login accounts
sys.server_principals ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN
--Permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
--Table columns
sys.columns col ON col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN
sys.objects obj ON perm.[major_id] = obj.[object_id]
LEFT JOIN
sys.schemas schem ON schem.[schema_id] = perm.[major_id]
LEFT JOIN
sys.database_principals imp ON imp.[principal_id] = perm.[major_id]
WHERE
princ.[type] IN ('S','U','G') AND
-- No need for these system accounts
princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')
这是我第一次根据安多马的建议回答问题。此查询旨在提供用户直接应用到用户帐户或通过该帐户应用的权限列表 用户拥有的角色。
/*
Security Audit Report
1) List all access provisioned to a sql user or windows user/group directly
2) List all access provisioned to a sql user or windows user/group through a database or application role
3) List all access provisioned to the public role
Columns Returned:
UserName : SQL or Windows/Active Directory user account. This could also be an Active Directory group.
UserType : Value will be either 'SQL User' or 'Windows User'. This reflects the type of user defined for the
SQL Server user account.
DatabaseUserName: Name of the associated user as defined in the database user account. The database user may not be the
same as the server user.
Role : The role name. This will be null if the associated permissions to the object are defined at directly
on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
ObjectType : Type of object the user/role is assigned permissions on. Examples could include USER_TABLE,
SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
ObjectName : Name of the object that the user/role is assigned permissions on.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
ColumnName : Name of the column of the object that the user/role is assigned permissions on. This value
is only populated if the object is a table, view or a table value function.
*/
--List all access provisioned to a sql user or windows user/group directly
SELECT
[UserName] = CASE princ.[type]
WHEN 'S' THEN princ.[name]
WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
END,
[UserType] = CASE princ.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
END,
[DatabaseUserName] = princ.[name],
[Role] = null,
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,--perm.[class_desc],
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
FROM
--database user
sys.database_principals princ
LEFT JOIN
--Login accounts
sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN
--Permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
--Table columns
sys.columns col ON col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN
sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE
princ.[type] in ('S','U')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT
[UserName] = CASE memberprinc.[type]
WHEN 'S' THEN memberprinc.[name]
WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
END,
[UserType] = CASE memberprinc.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
END,
[DatabaseUserName] = memberprinc.[name],
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,--perm.[class_desc],
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
FROM
--Role/member associations
sys.database_role_members members
JOIN
--Roles
sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
--Role members (database users)
sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
--Login accounts
sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN
--Permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
--Table columns
sys.columns col on col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN
sys.objects obj ON perm.[major_id] = obj.[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT
[UserName] = '{All Users}',
[UserType] = '{All Users}',
[DatabaseUserName] = '{All Users}',
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,--perm.[class_desc],
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
FROM
--Roles
sys.database_principals roleprinc
LEFT JOIN
--Role permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
--Table columns
sys.columns col on col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
JOIN
--All objects
sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
--Only roles
roleprinc.[type] = 'R' AND
--Only public role
roleprinc.[name] = 'public' AND
--Only objects of ours, not the MS objects
obj.is_ms_shipped = 0
ORDER BY
princ.[Name],
OBJECT_NAME(perm.major_id),
col.[name],
perm.[permission_name],
perm.[state_desc],
obj.type_desc--perm.[class_desc]
下面是Jeremy提交的最受欢迎的答案，但经过修改，包含了Greg Sipes提到的系统管理员和禁用标志以及log_date_time列。
两全其美?
/*
Source: https://stackoverflow.com/questions/7048839/sql-server-query-to-find-all-permissions-access-for-all-users-in-a-database
Security Audit Report
1) List all access provisioned to a sql user or windows user/group directly
2) List all access provisioned to a sql user or windows user/group through a database or application role
3) List all access provisioned to the public role
Columns Returned:
UserName : SQL or Windows/Active Directory user account. This could also be an Active Directory group.
UserType : Value will be either 'SQL User' or 'Windows User'. This reflects the type of user defined for the
SQL Server user account.
DatabaseUserName: Name of the associated user as defined in the database user account. The database user may not be the
same as the server user.
Role : The role name. This will be null if the associated permissions to the object are defined at directly
on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
ObjectType : Type of object the user/role is assigned permissions on. Examples could include USER_TABLE,
SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
ObjectName : Name of the object that the user/role is assigned permissions on.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
ColumnName : Name of the column of the object that the user/role is assigned permissions on. This value
is only populated if the object is a table, view or a table value function.
*/
--List all access provisioned to a sql user or windows user/group directly
SELECT
[UserName] = CASE princ.[type]
WHEN 'S' THEN princ.[name]
WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
END,
[UserType] = CASE princ.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
END,
[DatabaseUserName] = princ.[name],
[Role] = null,
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,--perm.[class_desc],
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name],
sp.is_disabled,
s.sysadmin,
GETDATE() AS [log_date_time]
FROM
--database user
sys.database_principals princ
LEFT JOIN
--Login accounts
sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN
--Permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
--Table columns
sys.columns col ON col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN
sys.objects obj ON perm.[major_id] = obj.[object_id]
LEFT JOIN sys.syslogins s ON princ.sid = s.sid
LEFT JOIN sys.server_principals sp ON princ.name = sp.name
WHERE
princ.[type] in ('S','U')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT
[UserName] = CASE memberprinc.[type]
WHEN 'S' THEN memberprinc.[name]
WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
END,
[UserType] = CASE memberprinc.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
END,
[DatabaseUserName] = memberprinc.[name],
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,--perm.[class_desc],
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name],
sp.is_disabled,
s.sysadmin,
GETDATE() AS [log_date_time]
FROM
--Role/member associations
sys.database_role_members members
JOIN
--Roles
sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
--Role members (database users)
sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
--Login accounts
sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN
--Permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
--Table columns
sys.columns col on col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN
sys.objects obj ON perm.[major_id] = obj.[object_id]
LEFT JOIN sys.syslogins s ON memberprinc.[sid] = s.sid
LEFT JOIN sys.server_principals sp ON memberprinc.[name] = sp.name
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT
[UserName] = '{All Users}',
[UserType] = '{All Users}',
[DatabaseUserName] = '{All Users}',
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,--perm.[class_desc],
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name],
sp.is_disabled,
s.sysadmin,
GETDATE() AS [log_date_time]
FROM
--Roles
sys.database_principals roleprinc
LEFT JOIN
--Role permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
--Table columns
sys.columns col on col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN sys.syslogins s ON roleprinc.sid = s.sid
LEFT JOIN sys.server_principals sp ON roleprinc.name = sp.name
JOIN
--All objects
sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
--Only roles
roleprinc.[type] = 'R' AND
--Only public role
roleprinc.[name] = 'public' AND
--Only objects of ours, not the MS objects
obj.is_ms_shipped = 0
ORDER BY
princ.[Name],
OBJECT_NAME(perm.major_id),
col.[name],
perm.[permission_name],
perm.[state_desc],
obj.type_desc--perm.[class_desc]
——好了，轮到我回馈了，好好享受吧
这个报表报头动态地获取SQL实例名称、日期\时间和运行报表的帐户名称，这些都是优秀的审计人员想知道的。：）
注意:如果你在Master数据库上有一个名为“environment”的扩展属性，这个值(不管你使用什么:PreProd, Development, Production, DR等)将包含在报告头中。
BEGIN
BEGIN TRY
SET NOCOUNT ON
SELECT 'See Messages Tab..... use Ctrl+SHIFT+F and re-run to ''send to file'''
DECLARE @DBName nvarchar(2000) = DB_NAME()
DECLARE @User_Name nvarchar(200) = suser_sname()
DECLARE @Account_Name nvarchar(200)
DECLARE @Granted_permissions nvarchar(2000)
DECLARE @Permission_State nvarchar(200)
DECLARE @ParentObject nvarchar(200)
DECLARE @env2 varchar(50) = Convert(varchar(50),(Select ServerProperty('Servername')));
DECLARE @day varchar(50) = FORMAT (getdate(), 'dddd, MM, yyyy');
DECLARE @clk varchar(50) = FORMAT (getdate(), 'hh:mm:ss tt') ;
DECLARE @env1 VARCHAR(25) = (SELECT CAST(value AS varchar(25))
FROM [master].[sys].fn_listextendedproperty('environment', default, default, default, default, default, default));
PRINT '*** ' + @DBName + ' Security Audit Report ***';
PRINT ' in the ' + @env1 + ' environment';
PRINT ' on SQL Instance: ' + @env2;
PRINT ' '+ @day + ' at ' + @clk;
PRINT ' run under account ' + @User_Name;
PRINT ' '
CREATE TABLE #GP(
DBName NVARCHAR(200),
Account_Name NVARCHAR(200),
Granted_Permissions NVARCHAR(max),
Permission_State NVARCHAR(200),
ParentObject NVARCHAR(200)
)
;WITH SampleDataR AS
(SELECT
DB_NAME() AS 'DBName'
,dp.name AS 'Account_Name'
,dpm.permission_name AS 'Granted_Permissions'
,dpm.state_desc AS 'Permission_State'
,dpm.class_desc AS 'ParentObject'
, ROW_NUMBER() OVER (PARTITION BY DB_NAME(), dp.[name] ,dpm.state_desc, dpm.class_desc ORDER BY permission_name) rownum
FROM sys.database_principals dp
LEFT OUTER JOIN [sys].[database_permissions] dpm
ON dp.principal_id = dpm.grantee_principal_id
WHERE dp.type ='R'
AND dp.sid IS NOT NULL
AND dp.name <> 'public'
AND dp.name NOT LIKE 'db_a%'
AND dp.name NOT LIKE 'db_b%'
AND dp.name NOT LIKE 'db_d%'
AND dp.name NOT LIKE 'db_o%'
AND dp.name NOT LIKE 'db_s%'
--AND dpm.class_desc = 'DATABASE' -- remove to see schema based permissions
)
--Select * from SampleDataR
INSERT INTO #GP
SELECT DISTINCT
DBName
,Account_Name
,(SELECT Granted_Permissions +
CASE
WHEN s1.rownum = (select MAX(rownum)
FROM SampleDataR
WHERE DBName = s1.DBName AND
Account_Name = s1.Account_Name AND
ParentObject = s1.ParentObject)
THEN ' (' + Permission_State + '), '
ELSE ' (' + Permission_State + '), '
END
FROM SampleDataR s1
WHERE s1.DBName = s2.DBName AND
s1.Account_Name = s2.Account_Name AND
s1.ParentObject = s2.ParentObject
FOR xml path(''),type).value('(.)[1]','varchar(max)'
) Granted_Permissions
,Permission_State
,ParentObject
FROM SampleDataR s2
--Select * from #GP
PRINT 'Assigned Role Permissions'
PRINT ' '
SET NOCOUNT ON
DECLARE cur CURSOR FOR
SELECT DISTINCT DBName, Account_Name, ParentObject, Granted_permissions
FROM #GP
OPEN cur
SET NOCOUNT ON
FETCH NEXT FROM cur INTO @DBname, @Account_Name, @ParentObject, @Granted_permissions;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @DBName + ', ' + @Account_Name + ', ' + '[' + @ParentObject + '], ' + @Granted_permissions
FETCH NEXT FROM cur INTO @DBname, @Account_Name, @ParentObject , @Granted_permissions;
END
CLOSE cur;
DEALLOCATE cur;
SET NOCOUNT ON
DROP Table #GP
SET NOCOUNT ON
DECLARE @DBName2 nvarchar(200)
DECLARE @Account_Name2 nvarchar(200)
DECLARE @Granted_permissions2 nvarchar(200)
CREATE TABLE #GP2(
DBName NVARCHAR(200),
Account_Name NVARCHAR(200) ,
Granted_Permissions NVARCHAR(200)
)
;WITH SampleDataR AS
(SELECT
DB_NAME() AS 'DBName'
,dp.name AS 'Account_Name'
--,dp.type
,dpm.permission_name
,ROW_NUMBER() OVER (PARTITION BY DB_NAME(), dp.[name] ORDER BY permission_name) rownum
FROM sys.database_principals dp
LEFT OUTER JOIN [sys].[database_permissions] dpm
ON dp.principal_id = dpm.grantee_principal_id
--order by dp.type
WHERE dp.type not in ('A', 'R', 'X') --removed 'G',
AND dp.sid is not null
AND dp.name not in ('guest','dbo')
)
INSERT INTO #GP2
SELECT DISTINCT
DBName
,Account_Name
,(SELECT permission_name +
CASE
WHEN s1.rownum = (select MAX(rownum)
FROM SampleDataR
WHERE DBName = s1.DBName and Account_Name = s1.Account_Name
)
THEN ''
ELSE ','
END
FROM SampleDataR s1
WHERE s1.DBName = s2.DBName AND s1.Account_Name = s2.Account_Name
FOR xml path(''),type).value('(.)[1]','varchar(max)') Granted_Permissions
FROM SampleDataR s2;
PRINT ' '
PRINT ' '
PRINT 'Assigned User Permissions'
PRINT ' '
DECLARE cur CURSOR FOR
SELECT DBName, Account_Name, Granted_permissions
FROM #GP2
OPEN cur
SET NOCOUNT ON
FETCH NEXT FROM cur INTO @DBname2, @Account_Name2, @Granted_permissions2;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @DBName2 + ', ' + @Account_Name2 + ', ' + @Granted_permissions2
FETCH NEXT FROM cur INTO @DBname2, @Account_Name2, @Granted_permissions2;
END
CLOSE cur;
DEALLOCATE cur;
DROP TABLE #GP2
SET NOCOUNT ON
DECLARE @DBName3 nvarchar(200)
DECLARE @Role_Name3 nvarchar(max)
DECLARE @Members3 nvarchar(max)
CREATE TABLE #GP3(
DBName NVARCHAR(200),
Role_Name NVARCHAR(max),
members NVARCHAR(max)
)
;WITH SampleDataR AS
(SELECT
DB_NAME() AS 'DBName'
,r.name AS 'role_name'
,m.name AS 'members'
,ROW_NUMBER() OVER (PARTITION BY DB_NAME(), r.[name] ORDER BY m.[name]) rownum
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
)
INSERT INTO #GP3
SELECT DISTINCT
DBName
,Role_Name
,(SELECT Members +
CASE
WHEN s3.rownum = (select MAX(rownum)
FROM SampleDataR
WHERE DBName = s3.DBName and Role_Name = s3.Role_Name
)
THEN ','
ELSE ','
END
FROM SampleDataR s1
WHERE s1.DBName = s3.DBName and s1.Role_Name = s3.Role_Name
FOR xml path(''),type).value('(.)[1]','varchar(max)') Members
FROM SampleDataR s3
PRINT ' '
PRINT ' '
PRINT 'Assigned Role Membership'
PRINT ' '
DECLARE cur CURSOR FOR
SELECT DBName, Role_Name, Members
FROM #GP3
OPEN cur
SET NOCOUNT ON
FETCH NEXT FROM cur INTO @DBname3, @Role_Name3, @Members3;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @DBName3 + ', ' + @Role_Name3 + ', ' + @Members3
FETCH NEXT FROM cur INTO @DBname3, @Role_Name3, @Members3;
END
CLOSE cur;
DEALLOCATE cur;
DROP Table #GP3;
END TRY
BEGIN CATCH
SELECT 'Real ERROR at Line #' + CAST(ERROR_LINE() AS VARCHAR(20));
-- Throw/raise and error caught from the Try section.
THROW;
END CATCH;
END
—保存为存储过程很棒
上面的GetPermissions存储过程很好，但是它使用Sp_msforeachdb，这意味着如果您的SQL实例有任何数据库名称，其中包括空格或虚线和其他非最佳实践字符，它将中断。我创建了一个避免使用Sp_msforeachdb的版本，它还包括两个列，分别表示1(如果登录是系统管理员登录(IsSysAdminLogin))和2(如果登录是孤儿用户(IsEmptyRow))。
USE [master] ;
GO
IF EXISTS
(
SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.uspGetPermissionsOfAllLogins_DBsOnColumns')
AND [type] in (N'P',N'PC')
)
BEGIN
DROP PROCEDURE dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ;
END
GO
CREATE PROCEDURE dbo.uspGetPermissionsOfAllLogins_DBsOnColumns
AS
SET NOCOUNT ON
;
BEGIN TRY
IF EXISTS
(
SELECT * FROM tempdb.dbo.sysobjects
WHERE id = object_id(N'[tempdb].dbo.[#permission]')
)
DROP TABLE #permission
;
IF EXISTS
(
SELECT * FROM tempdb.dbo.sysobjects
WHERE id = object_id(N'[tempdb].dbo.[#userroles_kk]')
)
DROP TABLE #userroles_kk
;
IF EXISTS
(
SELECT * FROM tempdb.dbo.sysobjects
WHERE id = object_id(N'[tempdb].dbo.[#rolemember_kk]')
)
DROP TABLE #rolemember_kk
;
IF EXISTS
(
SELECT * FROM tempdb.dbo.sysobjects
WHERE id = object_id(N'[tempdb].dbo.[##db_name]')
)
DROP TABLE ##db_name
;
DECLARE
@db_name VARCHAR(255)
,@sql_text VARCHAR(MAX)
;
SET @sql_text =
'CREATE TABLE ##db_name
(
LoginUserName VARCHAR(MAX)
,'
;
DECLARE cursDBs CURSOR FOR
SELECT [name]
FROM sys.databases
ORDER BY [name]
;
OPEN cursDBs
;
FETCH NEXT FROM cursDBs INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql_text =
@sql_text + QUOTENAME(@db_name) + ' VARCHAR(MAX)
,'
FETCH NEXT FROM cursDBs INTO @db_name
END
CLOSE cursDBs
;
SET @sql_text =
@sql_text + 'IsSysAdminLogin CHAR(1)
,IsEmptyRow CHAR(1)
)'
--PRINT @sql_text
EXEC (@sql_text)
;
DEALLOCATE cursDBs
;
DECLARE
@RoleName VARCHAR(255)
,@UserName VARCHAR(255)
;
CREATE TABLE #permission
(
LoginUserName VARCHAR(255)
,databasename VARCHAR(255)
,[role] VARCHAR(255)
)
;
DECLARE cursSysSrvPrinName CURSOR FOR
SELECT [name]
FROM sys.server_principals
WHERE
[type] IN ( 'S', 'U', 'G' )
AND principal_id > 4
AND [name] NOT LIKE '##%'
ORDER BY [name]
;
OPEN cursSysSrvPrinName
;
FETCH NEXT FROM cursSysSrvPrinName INTO @UserName
WHILE @@FETCH_STATUS = 0
BEGIN
CREATE TABLE #userroles_kk
(
databasename VARCHAR(255)
,[role] VARCHAR(255)
)
;
CREATE TABLE #rolemember_kk
(
dbrole VARCHAR(255)
,membername VARCHAR(255)
,membersid VARBINARY(2048)
)
;
DECLARE cursDatabases CURSOR FAST_FORWARD LOCAL FOR
SELECT [name]
FROM sys.databases
ORDER BY [name]
;
OPEN cursDatabases
;
DECLARE
@DBN VARCHAR(255)
,@sqlText NVARCHAR(4000)
;
FETCH NEXT FROM cursDatabases INTO @DBN
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlText =
N'USE ' + QUOTENAME(@DBN) + ';
TRUNCATE TABLE #RoleMember_kk
INSERT INTO #RoleMember_kk
EXEC sp_helprolemember
INSERT INTO #UserRoles_kk
(DatabaseName,[Role])
SELECT db_name(),dbRole
FROM #RoleMember_kk
WHERE MemberName = ''' + @UserName + '''
'
--PRINT @sqlText ;
EXEC sp_executesql @sqlText ;
FETCH NEXT FROM cursDatabases INTO @DBN
END
CLOSE cursDatabases
;
DEALLOCATE cursDatabases
;
INSERT INTO #permission
SELECT
@UserName 'user'
,b.name
,u.[role]
FROM
sys.sysdatabases b
LEFT JOIN
#userroles_kk u
ON QUOTENAME(u.databasename) = QUOTENAME(b.name)
ORDER BY 1
;
DROP TABLE #userroles_kk
;
DROP TABLE #rolemember_kk
;
FETCH NEXT FROM cursSysSrvPrinName INTO @UserName
END
CLOSE cursSysSrvPrinName
;
DEALLOCATE cursSysSrvPrinName
;
TRUNCATE TABLE ##db_name
;
DECLARE
@d1 VARCHAR(MAX)
,@d2 VARCHAR(MAX)
,@d3 VARCHAR(MAX)
,@ss VARCHAR(MAX)
;
DECLARE cursPermisTable CURSOR FOR
SELECT * FROM #permission
ORDER BY 2 DESC
;
OPEN cursPermisTable
;
FETCH NEXT FROM cursPermisTable INTO @d1,@d2,@d3
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS
(
SELECT 1 FROM ##db_name WHERE LoginUserName = @d1
)
BEGIN
SET @ss =
'INSERT INTO ##db_name(LoginUserName) VALUES (''' + @d1 + ''')'
EXEC (@ss)
;
SET @ss =
'UPDATE ##db_name SET ' + @d2 + ' = ''' + @d3 + ''' WHERE LoginUserName = ''' + @d1 + ''''
EXEC (@ss)
;
END
ELSE
BEGIN
DECLARE
@var NVARCHAR(MAX)
,@ParmDefinition NVARCHAR(MAX)
,@var1 NVARCHAR(MAX)
;
SET @var =
N'SELECT @var1 = ' + QUOTENAME(@d2) + ' FROM ##db_name WHERE LoginUserName = ''' + @d1 + ''''
;
SET @ParmDefinition =
N'@var1 NVARCHAR(600) OUTPUT '
;
EXECUTE Sp_executesql @var,@ParmDefinition,@var1 = @var1 OUTPUT
;
SET @var1 =
ISNULL(@var1, ' ')
;
SET @var =
' UPDATE ##db_name SET ' + @d2 + '=''' + @var1 + ' ' + @d3 + ''' WHERE LoginUserName = ''' + @d1 + ''' '
;
EXEC (@var)
;
END
FETCH NEXT FROM cursPermisTable INTO @d1,@d2,@d3
END
CLOSE cursPermisTable
;
DEALLOCATE cursPermisTable
;
UPDATE ##db_name SET
IsSysAdminLogin = 'Y'
FROM
##db_name TT
INNER JOIN
dbo.syslogins SL
ON TT.LoginUserName = SL.[name]
WHERE
SL.sysadmin = 1
;
DECLARE cursDNamesAsColumns CURSOR FAST_FORWARD LOCAL FOR
SELECT [name]
FROM tempdb.sys.columns
WHERE
OBJECT_ID = OBJECT_ID('tempdb..##db_name')
AND [name] NOT IN ('LoginUserName','IsEmptyRow')
ORDER BY [name]
;
OPEN cursDNamesAsColumns
;
DECLARE
@ColN VARCHAR(255)
,@tSQLText NVARCHAR(4000)
;
FETCH NEXT FROM cursDNamesAsColumns INTO @ColN
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tSQLText =
N'UPDATE ##db_name SET
IsEmptyRow = ''N''
WHERE IsEmptyRow IS NULL
AND ' + QUOTENAME(@ColN) + ' IS NOT NULL
;
'
--PRINT @tSQLText ;
EXEC sp_executesql @tSQLText ;
FETCH NEXT FROM cursDNamesAsColumns INTO @ColN
END
CLOSE cursDNamesAsColumns
;
DEALLOCATE cursDNamesAsColumns
;
UPDATE ##db_name SET
IsEmptyRow = 'Y'
WHERE IsEmptyRow IS NULL
;
UPDATE ##db_name SET
IsSysAdminLogin = 'N'
FROM
##db_name TT
INNER JOIN
dbo.syslogins SL
ON TT.LoginUserName = SL.[name]
WHERE
SL.sysadmin = 0
;
SELECT * FROM ##db_name
;
DROP TABLE ##db_name
;
DROP TABLE #permission
;
END TRY
BEGIN CATCH
DECLARE
@cursDBs_Status INT
,@cursSysSrvPrinName_Status INT
,@cursDatabases_Status INT
,@cursPermisTable_Status INT
,@cursDNamesAsColumns_Status INT
;
SELECT
@cursDBs_Status = CURSOR_STATUS('GLOBAL','cursDBs')
,@cursSysSrvPrinName_Status = CURSOR_STATUS('GLOBAL','cursSysSrvPrinName')
,@cursDatabases_Status = CURSOR_STATUS('GLOBAL','cursDatabases')
,@cursPermisTable_Status = CURSOR_STATUS('GLOBAL','cursPermisTable')
,@cursDNamesAsColumns_Status = CURSOR_STATUS('GLOBAL','cursPermisTable')
;
IF @cursDBs_Status > -2
BEGIN
CLOSE cursDBs ;
DEALLOCATE cursDBs ;
END
IF @cursSysSrvPrinName_Status > -2
BEGIN
CLOSE cursSysSrvPrinName ;
DEALLOCATE cursSysSrvPrinName ;
END
IF @cursDatabases_Status > -2
BEGIN
CLOSE cursDatabases ;
DEALLOCATE cursDatabases ;
END
IF @cursPermisTable_Status > -2
BEGIN
CLOSE cursPermisTable ;
DEALLOCATE cursPermisTable ;
END
IF @cursDNamesAsColumns_Status > -2
BEGIN
CLOSE cursDNamesAsColumns ;
DEALLOCATE cursDNamesAsColumns ;
END
SELECT ErrorNum = ERROR_NUMBER(),ErrorMsg = ERROR_MESSAGE() ;
END CATCH
GO
/*
EXEC [master].dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ;
*/
推荐文章
- 选项(RECOMPILE)总是更快;为什么?
- 设置数据库从单用户模式到多用户
- 我如何转义一个百分比符号在T-SQL?
- SQL Server恢复错误-拒绝访问
- 的类型不能用作索引中的键列
- 如何在SQL Server Management Studio中查看查询历史
- SQL Server索引命名约定
- 可以为公共表表达式创建嵌套WITH子句吗?
- SQL Server的NOW()?
- 在SQL Server中截断(不是四舍五入)小数位
- 如何在SQL Server数据库中更改列数据类型而不丢失数据?
- 保持简单，以及如何在一个查询中执行多个CTE
- 如何使用TSQL截断数据库中的所有表?
- 如何从表中导出所有数据到可插入的sql格式?
- 我如何执行插入和返回插入的身份与Dapper?
