我想在sql 2008上写一个查询，它将报告所有有权访问特定数据库的用户，或数据库中的对象，如表，视图和存储过程，直接或由于角色等。该报告将用于安全审计目的。不确定是否有人有一个查询，将完全满足我的需求，但希望能给我一个好的开始。无论是sql 2008, 2005或2000将做，我可以根据需要转换。
当前回答
一个简单的查询，只显示你是否是系统管理员:
IF IS_SRVROLEMEMBER ('sysadmin') = 1
print 'Current user''s login is a member of the sysadmin role'
ELSE IF IS_SRVROLEMEMBER ('sysadmin') = 0
print 'Current user''s login is NOT a member of the sysadmin role'
ELSE IF IS_SRVROLEMEMBER ('sysadmin') IS NULL
print 'ERROR: The server role specified is not valid.';
其他回答
由于低代表不能与此回复的人要求在多个数据库/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
这个帖子极大地帮助了我，谢谢大家!
一个简单的查询，只显示你是否是系统管理员:
IF IS_SRVROLEMEMBER ('sysadmin') = 1
print 'Current user''s login is a member of the sysadmin role'
ELSE IF IS_SRVROLEMEMBER ('sysadmin') = 0
print 'Current user''s login is NOT a member of the sysadmin role'
ELSE IF IS_SRVROLEMEMBER ('sysadmin') IS NULL
print 'ERROR: The server role specified is not valid.';
以下是Jeremy在2011年8月的查询的完整版本，其中包含了Brad(2011年10月)和iw提出的修改建议。kuchin(2012年5月)注册成立:
Brad:正确的模式[ObjectType]和[ObjectName]。 信息战。kuchin:对于[ObjectType]，最好使用obj。type_desc仅用于OBJECT_OR_COLUMN权限类。对于所有其他情况，请使用perm.[class_desc]。 信息战。kuchin:处理IMPERSONATE权限。 信息战。“kuchin”:替换“sys”。Login_token与sys。server_principals，因为它还将显示SQL登录，而不仅仅是Windows登录。 信息战。kuchin:包含Windows组。 信息战。kuchin:排除“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:
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]
下面是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]
我只是在Jeremy的回答中添加了以下内容，因为我有一个分配给数据库db_datareader的角色，该角色没有显示该角色拥有的权限。我试着查看每个人帖子中的所有答案，但找不到任何可以做到这一点的答案，所以我添加了自己的问题。
SELECT
UserType='Role',
DatabaseUserName = '{Role Members}',
LoginName = DP2.name,
Role = DP1.name,
'SELECT' AS [PermissionType] ,
[PermissionState] = 'GRANT',
[ObjectType] = 'Table',
[Schema] = 'dbo',
[ObjectName] = 'All Tables',
[ColumnName] = NULL
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
AND DP2.name IS NOT NULL
推荐文章
- 确定记录是否存在的最快方法
- 从现有模式生成表关系图(SQL Server)
- 我如何循环通过一组记录在SQL Server?
- 数据库和模式的区别
- 如何在SQL Server中一次更改多个列
- 外键约束可能导致循环或多条级联路径?
- 如何选择每一行的列值不是独特的
- nvarchar(max)非文本
- Android M权限:对shouldShowRequestPermissionRationale()函数的使用感到困惑
- 在SQL Server 2008 R2中重命名数据库时出错
- 将数据复制到另一个表中
- 如何在SQL中选择表的最后一条记录?
- 修改列，添加默认约束
- 在存储过程中使用“SET XACT_ABORT ON”有什么好处?
- 如何检查SQL Server文本列是否为空?
