我想在sql 2008上写一个查询，它将报告所有有权访问特定数据库的用户，或数据库中的对象，如表，视图和存储过程，直接或由于角色等。该报告将用于安全审计目的。不确定是否有人有一个查询，将完全满足我的需求，但希望能给我一个好的开始。无论是sql 2008, 2005或2000将做，我可以根据需要转换。
当前回答
以下是我的版本，改编自他人。我刚刚花了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 Server 2005开始，您可以使用系统视图来实现这一点。例如，这个查询列出了数据库中的所有用户及其权限:
select princ.name
, princ.type_desc
, perm.permission_name
, perm.state_desc
, perm.class_desc
, object_name(perm.major_id)
from sys.database_principals princ
left join
sys.database_permissions perm
on perm.grantee_principal_id = princ.principal_id
请注意，用户也可以通过角色拥有权限。例如，db_data_reader角色授予对大多数对象的选择权限。
我看到的其他答案遗漏了数据库中可能存在的一些权限。下面代码中的第一个查询将获得非系统对象的数据库级权限。它还生成适当的GRANT语句。第二个查询获取所有角色成员。
这必须为每个数据库运行，但是太长了，不能用于sp_MSforeachdb。如果要这样做，就必须将它作为系统存储过程添加到主数据库中。
为了涵盖所有可能性，还必须有一个检查服务器级权限的脚本。
SELECT DB_NAME() AS database_name
, class
, class_desc
, major_id
, minor_id
, grantee_principal_id
, grantor_principal_id
, databasepermissions.type
, permission_name
, STATE
, state_desc
, granteedatabaseprincipal.name AS grantee_name
, granteedatabaseprincipal.type_desc AS grantee_type_desc
, granteeserverprincipal.name AS grantee_principal_name
, granteeserverprincipal.type_desc AS grantee_principal_type_desc
, grantor.name AS grantor_name
, granted_on_name
, permissionstatement + N' TO ' + QUOTENAME(granteedatabaseprincipal.name) + CASE
WHEN STATE = N'W'
THEN N' WITH GRANT OPTION'
ELSE N''
END AS permissionstatement
FROM (
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(CONVERT(NVARCHAR(MAX), DB_NAME())) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS permissionstatement
FROM sys.database_permissions
WHERE (sys.database_permissions.class = 0)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.schemas.name) + N'.' + QUOTENAME(sys.objects.name) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ' + QUOTENAME(sys.schemas.name) + N'.' + QUOTENAME(sys.objects.name) + COALESCE(N' (' + QUOTENAME(sys.columns.name) + N')', N'') AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.objects
ON sys.objects.object_id = sys.database_permissions.major_id
INNER JOIN sys.schemas
ON sys.schemas.schema_id = sys.objects.schema_id
LEFT OUTER JOIN sys.columns
ON sys.columns.object_id = sys.database_permissions.major_id
AND sys.columns.column_id = sys.database_permissions.minor_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 1)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.schemas.name) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON SCHEMA::' + QUOTENAME(sys.schemas.name) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.schemas
ON sys.schemas.schema_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 3)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(targetPrincipal.name) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ' + targetPrincipal.type_desc + N'::' + QUOTENAME(targetPrincipal.name) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.database_principals AS targetPrincipal
ON targetPrincipal.principal_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 4)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.assemblies.name) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ASSEMBLY::' + QUOTENAME(sys.assemblies.name) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.assemblies
ON sys.assemblies.assembly_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 5)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.types.name) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON TYPE::' + QUOTENAME(sys.types.name) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.types
ON sys.types.user_type_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 6)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.types.name) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON TYPE::' + QUOTENAME(sys.types.name) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.types
ON sys.types.user_type_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 6)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.xml_schema_collections.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON XML SCHEMA COLLECTION::' + QUOTENAME(sys.xml_schema_collections.name) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.xml_schema_collections
ON sys.xml_schema_collections.xml_collection_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 10)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.service_message_types.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON MESSAGE TYPE::' + QUOTENAME(sys.service_message_types.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.service_message_types
ON sys.service_message_types.message_type_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 15)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.service_contracts.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON CONTRACT::' + QUOTENAME(sys.service_contracts.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.service_contracts
ON sys.service_contracts.service_contract_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 16)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.services.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON SERVICE::' + QUOTENAME(sys.services.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.services
ON sys.services.service_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 17)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.remote_service_bindings.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON REMOTE SERVICE BINDING::' + QUOTENAME(sys.remote_service_bindings.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.remote_service_bindings
ON sys.remote_service_bindings.remote_service_binding_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 18)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.routes.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ROUTE::' + QUOTENAME(sys.routes.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.routes
ON sys.routes.route_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 19)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.symmetric_keys.name) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ASYMMETRIC KEY::' + QUOTENAME(sys.symmetric_keys.name) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.symmetric_keys
ON sys.symmetric_keys.symmetric_key_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 24)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.certificates.name) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON CERTIFICATE::' + QUOTENAME(sys.certificates.name) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.certificates
ON sys.certificates.certificate_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 25)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.asymmetric_keys.name) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ASYMMETRIC KEY::' + QUOTENAME(sys.asymmetric_keys.name) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.asymmetric_keys
ON sys.asymmetric_keys.asymmetric_key_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 26)
) AS databasepermissions
INNER JOIN sys.database_principals AS granteedatabaseprincipal
ON granteedatabaseprincipal.principal_id = grantee_principal_id
LEFT OUTER JOIN sys.server_principals AS granteeserverprincipal
ON granteeserverprincipal.sid = granteedatabaseprincipal.sid
INNER JOIN sys.database_principals AS grantor
ON grantor.principal_id = grantor_principal_id
ORDER BY grantee_name, granted_on_name
SELECT roles.name AS role_name
, roles.principal_id
, roles.type AS role_type
, roles.type_desc AS role_type_desc
, roles.is_fixed_role AS role_is_fixed_role
, memberdatabaseprincipal.name AS member_name
, memberdatabaseprincipal.principal_id AS member_principal_id
, memberdatabaseprincipal.type AS member_type
, memberdatabaseprincipal.type_desc AS member_type_desc
, memberdatabaseprincipal.is_fixed_role AS member_is_fixed_role
, memberserverprincipal.name AS member_principal_name
, memberserverprincipal.type_desc member_principal_type_desc
, N'ALTER ROLE ' + QUOTENAME(roles.name) + N' ADD MEMBER ' + QUOTENAME(memberdatabaseprincipal.name) AS AddRoleMembersStatement
FROM sys.database_principals AS roles
INNER JOIN sys.database_role_members
ON sys.database_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.database_principals AS memberdatabaseprincipal
ON memberdatabaseprincipal.principal_id = sys.database_role_members.member_principal_id
LEFT OUTER JOIN sys.server_principals AS memberserverprincipal
ON memberserverprincipal.sid = memberdatabaseprincipal.sid
ORDER BY role_name
, member_name
更新:以下查询将检索服务器级别的权限和成员。
SELECT sys.server_permissions.class
, sys.server_permissions.class_desc
, sys.server_permissions.major_id
, sys.server_permissions.minor_id
, sys.server_permissions.grantee_principal_id
, sys.server_permissions.grantor_principal_id
, sys.server_permissions.type
, sys.server_permissions.permission_name
, sys.server_permissions.state
, sys.server_permissions.state_desc
, granteeserverprincipal.name AS grantee_principal_name
, granteeserverprincipal.type_desc AS grantee_principal_type_desc
, grantorserverprinicipal.name AS grantor_name
, CASE
WHEN sys.server_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.server_permissions.state_desc
END + N' ' + sys.server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' TO ' + QUOTENAME(granteeserverprincipal.name) AS permissionstatement
FROM sys.server_principals AS granteeserverprincipal
INNER JOIN sys.server_permissions
ON sys.server_permissions.grantee_principal_id = granteeserverprincipal.principal_id
INNER JOIN sys.server_principals AS grantorserverprinicipal
ON grantorserverprinicipal.principal_id = sys.server_permissions.grantor_principal_id
ORDER BY granteeserverprincipal.name
, sys.server_permissions.permission_name
SELECT roles.name AS server_role_name
, roles.principal_id
, roles.type AS role_type
, roles.type_desc AS role_type_desc
, roles.is_fixed_role AS role_is_fixed_role
, memberserverprincipal.name AS member_principal_name
, memberserverprincipal.principal_id AS member_principal_id
, memberserverprincipal.type AS member_principal_type
, memberserverprincipal.type_desc AS member_principal_type_desc
, memberserverprincipal.is_fixed_role AS member_is_fixed_role
, N'ALTER SERVER ROLE ' + QUOTENAME(roles.name) + N' ADD MEMBER ' + QUOTENAME(memberserverprincipal.name) AS AddRoleMembersStatement
FROM sys.server_principals AS roles
INNER JOIN sys.server_role_members
ON sys.server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.server_principals AS memberserverprincipal
ON memberserverprincipal.principal_id = sys.server_role_members.member_principal_id
WHERE roles.type = N'R'
ORDER BY server_role_name
, member_principal_name
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
我只是在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
上面的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?
