我想在sql 2008上写一个查询，它将报告所有有权访问特定数据库的用户，或数据库中的对象，如表，视图和存储过程，直接或由于角色等。该报告将用于安全审计目的。不确定是否有人有一个查询，将完全满足我的需求，但希望能给我一个好的开始。无论是sql 2008, 2005或2000将做，我可以根据需要转换。
当前回答
非常感谢出色的审计脚本。
我强烈建议审计用户使用很棒的Kenneth Fisher (b | t)存储过程:
sp_DBPermissions sp_SrvPermissions
其他回答
我尝试了几乎所有这些功能，但很快就注意到缺少一些功能，尤其是系统管理员用户。在我们即将进行的审计中，有这样一个洞会很不好看，所以我想出了这个
USE master
GO
SELECT DISTINCT
p.name AS [loginname] ,
--p.type,
p.type_desc ,
p.is_disabled,
s.sysadmin,
sp.permission_name
FROM sys.server_principals p
INNER JOIN sys.syslogins s ON p.sid = s.sid
INNER JOIN sys.server_permissions sp ON p.principal_id = sp.grantee_principal_id
WHERE p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
-- Logins that are not process logins
AND p.name NOT LIKE '##%'
ORDER BY p.name
GO
无法评论已接受的答案，所以我将在这里添加一些评论:
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')
——好了，轮到我回馈了，好好享受吧
这个报表报头动态地获取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
—保存为存储过程很棒
以下是我的版本，改编自他人。我刚刚花了30分钟来回忆我是怎么想到这个的，@杰里米的回答似乎是我的核心灵感。我不想更新杰里米的答案，以防我引入错误，所以我在这里发布了我的版本。
我建议将完整脚本与Kenneth Fisher的《T-SQL Tuesday:特定用户拥有什么权限?》:这将允许你自底向上回答合规/审计问题，而不是自顶向下。
EXECUTE AS LOGIN = '<loginname>'
SELECT token.name AS GroupNames
FROM sys.login_token token
JOIN sys.server_principals grp
ON token.sid = grp.sid
WHERE token.[type] = 'WINDOWS GROUP'
AND grp.[type] = 'G'
REVERT
To understand what this covers, consider Contoso\DB_AdventureWorks_Accounting Windows AD Group with member Contoso\John.Doe. John.Doe authenticates to AdventureWorks via server_principal Contoso\DB_AdventureWorks_Logins Windows AD Group. If someone asks you, "What permissions does John.Doe have?", you cannot answer that question with just the below script. You need to then iterate through each row returned by the below script and join it to the above script. (You may also need to normalize for stale name values via looking up the SID in your Active Directory provider.)
下面是脚本，没有包含这种反向查找逻辑。
/*
--Script source found at : http://stackoverflow.com/a/7059579/1387418
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.
PrinciaplUserName: if UserName is not blank, then UserName else DatabaseUserName
PrincipalType : Possible values are 'SQL User', 'Windows User', 'Database Role', 'Windows Group'
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.
*/
DECLARE @HideDatabaseDiagrams BIT = 1;
--List all access provisioned to a sql user or windows user/group directly
SELECT
[UserName] = CASE dbprinc.[type]
WHEN 'S' THEN dbprinc.[name] -- SQL User
WHEN 'U' THEN sprinc.[name] -- Windows User
WHEN 'R' THEN NULL -- Database Role
WHEN 'G' THEN NULL -- Windows Group
ELSE NULL
END,
[UserType] = CASE dbprinc.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'R' THEN NULL -- Database Role
WHEN 'G' THEN NULL -- Windows Group
ELSE dbprinc.[type]
END,
[PrincipalUserName] = COALESCE(
CASE dbprinc.[type]
WHEN 'S' THEN dbprinc.[name] -- SQL User
WHEN 'U' THEN sprinc.[name] -- Windows User
WHEN 'R' THEN NULL -- Database Role
WHEN 'G' THEN NULL -- Windows Group
ELSE NULL
END,
dbprinc.[name]
),
[PrincipalType] = CASE dbprinc.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'R' THEN 'Database Role'
WHEN 'G' THEN 'Windows Group'
END,
[DatabaseUserName] = dbprinc.[name],
[Role] = null,
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.[type_desc],--perm.[class_desc],
[ObjectSchema] = OBJECT_SCHEMA_NAME(perm.major_id),
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
FROM
--database user
sys.database_principals dbprinc
LEFT JOIN
--Login accounts
sys.server_principals sprinc on dbprinc.[sid] = sprinc.[sid]
LEFT JOIN
--Permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = dbprinc.[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
dbprinc.[type] in ('S','U')
AND CASE
WHEN @HideDatabaseDiagrams = 1 AND
dbprinc.[name] = 'guest'
AND (
(
obj.type_desc = 'SQL_SCALAR_FUNCTION'
AND OBJECT_NAME(perm.major_id) = 'fn_diagramobjects'
)
OR (
obj.type_desc = 'SQL_STORED_PROCEDURE'
AND OBJECT_NAME(perm.major_id) IN
(
N'sp_alterdiagram',
N'sp_creatediagram',
N'sp_dropdiagram',
N'sp_helpdiagramdefinition',
N'sp_helpdiagrams',
N'sp_renamediagram'
)
)
)
THEN 0
ELSE 1
END = 1
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 sprinc.[name]
WHEN 'R' THEN NULL -- Database Role
WHEN 'G' THEN NULL -- Windows Group
ELSE NULL
END,
[UserType] = CASE memberprinc.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'R' THEN NULL -- Database Role
WHEN 'G' THEN NULL -- Windows Group
END,
[PrincipalUserName] = COALESCE(
CASE memberprinc.[type]
WHEN 'S' THEN memberprinc.[name]
WHEN 'U' THEN sprinc.[name]
WHEN 'R' THEN NULL -- Database Role
WHEN 'G' THEN NULL -- Windows Group
ELSE NULL
END,
memberprinc.[name]
),
[PrincipalType] = CASE memberprinc.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'R' THEN 'Database Role'
WHEN 'G' THEN 'Windows Group'
END,
[DatabaseUserName] = memberprinc.[name],
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,--perm.[class_desc],
[ObjectSchema] = OBJECT_SCHEMA_NAME(perm.major_id),
[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.server_principals sprinc on memberprinc.[sid] = sprinc.[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]
WHERE
CASE
WHEN @HideDatabaseDiagrams = 1 AND
memberprinc.[name] = 'guest'
AND (
(
obj.type_desc = 'SQL_SCALAR_FUNCTION'
AND OBJECT_NAME(perm.major_id) = 'fn_diagramobjects'
)
OR (
obj.type_desc = 'SQL_STORED_PROCEDURE'
AND OBJECT_NAME(perm.major_id) IN
(
N'sp_alterdiagram',
N'sp_creatediagram',
N'sp_dropdiagram',
N'sp_helpdiagramdefinition',
N'sp_helpdiagrams',
N'sp_renamediagram'
)
)
)
THEN 0
ELSE 1
END = 1
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT
[UserName] = '{All Users}',
[UserType] = '{All Users}',
[PrincipalUserName] = '{All Users}',
[PrincipalType] = '{All Users}',
[DatabaseUserName] = '{All Users}',
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,--perm.[class_desc],
[ObjectSchema] = OBJECT_SCHEMA_NAME(perm.major_id),
[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
AND CASE
WHEN @HideDatabaseDiagrams = 1 AND
roleprinc.[name] = 'public'
AND (
(
obj.type_desc = 'SQL_SCALAR_FUNCTION'
AND OBJECT_NAME(perm.major_id) = 'fn_diagramobjects'
)
OR (
obj.type_desc = 'SQL_STORED_PROCEDURE'
AND OBJECT_NAME(perm.major_id) IN
(
N'sp_alterdiagram',
N'sp_creatediagram',
N'sp_dropdiagram',
N'sp_helpdiagramdefinition',
N'sp_helpdiagrams',
N'sp_renamediagram'
)
)
)
THEN 0
ELSE 1
END = 1
ORDER BY
dbprinc.[Name],
OBJECT_NAME(perm.major_id),
col.[name],
perm.[permission_name],
perm.[state_desc],
obj.type_desc--perm.[class_desc]
由于低代表不能与此回复的人要求在多个数据库/SQL服务器上运行此。
创建一个注册的服务器组，并查询他们所有的我们以下和只是游标通过数据库:
--Make sure all ' are doubled within the SQL string.
DECLARE @dbname VARCHAR(50)
DECLARE @statement NVARCHAR(max)
DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD
FOR
SELECT name
FROM MASTER.dbo.sysdatabases
where name like '%DBName%'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @statement = 'use '+@dbname +';'+ '
/*
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:
UserType : Value will be either ''SQL User'', ''Windows User'', or ''Windows Group''.
This reflects the type of user/group defined for the SQL Server 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.
LoginName : SQL or Windows/Active Directory user account. This could also be an Active Directory group.
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.
Schema : Name of the schema the object is in.
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.
*/
--1) List all access provisioned to a SQL user or Windows user/group directly
SELECT
[UserType] = CASE princ.[type]
WHEN ''S'' THEN ''SQL User''
WHEN ''U'' THEN ''Windows User''
WHEN ''G'' THEN ''Windows Group''
END,
[DatabaseUserName] = princ.[name],
[LoginName] = ulogin.[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,
[Schema] = objschem.[name],
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name] -- Schemas
WHEN 4 THEN imp.[name] -- Impersonations
ELSE OBJECT_NAME(perm.[major_id]) -- General objects
END,
[ColumnName] = col.[name]
FROM
--Database user
sys.database_principals AS princ
--Login accounts
LEFT JOIN sys.server_principals AS ulogin ON ulogin.[sid] = princ.[sid]
--Permissions
LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id]
LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id]
LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id]
--Table columns
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
--Impersonations
LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id]
WHERE
princ.[type] IN (''S'',''U'',''G'')
-- No need for these system accounts
AND princ.[name] NOT IN (''sys'', ''INFORMATION_SCHEMA'')
UNION
--2) List all access provisioned to a SQL user or Windows user/group through a database or application role
SELECT
[UserType] = CASE membprinc.[type]
WHEN ''S'' THEN ''SQL User''
WHEN ''U'' THEN ''Windows User''
WHEN ''G'' THEN ''Windows Group''
END,
[DatabaseUserName] = membprinc.[name],
[LoginName] = ulogin.[name],
[Role] = roleprinc.[name],
[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,
[Schema] = objschem.[name],
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name] -- Schemas
WHEN 4 THEN imp.[name] -- Impersonations
ELSE OBJECT_NAME(perm.[major_id]) -- General objects
END,
[ColumnName] = col.[name]
FROM
--Role/member associations
sys.database_role_members AS members
--Roles
JOIN sys.database_principals AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
--Role members (database users)
JOIN sys.database_principals AS membprinc ON membprinc.[principal_id] = members.[member_principal_id]
--Login accounts
LEFT JOIN sys.server_principals AS ulogin ON ulogin.[sid] = membprinc.[sid]
--Permissions
LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id]
LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id]
LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id]
--Table columns
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
--Impersonations
LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id]
WHERE
membprinc.[type] IN (''S'',''U'',''G'')
-- No need for these system accounts
AND membprinc.[name] NOT IN (''sys'', ''INFORMATION_SCHEMA'')
UNION
--3) List all access provisioned to the public role, which everyone gets by default
SELECT
[UserType] = ''{All Users}'',
[DatabaseUserName] = ''{All Users}'',
[LoginName] = ''{All Users}'',
[Role] = roleprinc.[name],
[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,
[Schema] = objschem.[name],
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name] -- Schemas
WHEN 4 THEN imp.[name] -- Impersonations
ELSE OBJECT_NAME(perm.[major_id]) -- General objects
END,
[ColumnName] = col.[name]
FROM
--Roles
sys.database_principals AS roleprinc
--Role permissions
LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id]
--All objects
JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id]
LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id]
--Table columns
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
--Impersonations
LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id]
WHERE
roleprinc.[type] = ''R''
AND roleprinc.[name] = ''public''
AND obj.[is_ms_shipped] = 0
ORDER BY
[UserType],
[DatabaseUserName],
[LoginName],
[Role],
[Schema],
[ObjectName],
[ColumnName],
[PermissionType],
[PermissionState],
[ObjectType]
'
exec sp_executesql @statement
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
这个帖子极大地帮助了我，谢谢大家!
推荐文章
- 如何检查SQL Server文本列是否为空?
- 如何创建一个SQL Server函数“连接”多行从一个子查询到一个单独的分隔字段?
- 在SQL中更新多个列
- 如何删除表中特定列的第一个字符?
- 等价的限制和偏移SQL Server?
- 如何从SQL Server中的字符串中剥离所有非字母字符?
- PostgreSQL:角色不允许登录
- 为什么我不能在DELETE语句中使用别名?
- 在SQL Server Management Studio中保存带有标题的结果
- 多语句表值函数vs内联表值函数
- NOLOCK提示在SELECT语句中的作用
- SQL OVER()子句-它什么时候有用，为什么有用?
- 检查SQL Server登录是否已经存在
- 我如何使用ROW_NUMBER()?
- 如何检查表上持有哪些锁
