当我选择脚本对象级权限选项时,Sql Server Management Studio 2008没有编写表权限的脚本。这是一个错误还是有其他方法可以解决?它正在为存储过程创建权限,但不是为表创建。我是系统管理员。
如果无法解决,是否有免费的sql server工具可用于脚本化权限?
当我选择脚本对象级权限选项时,Sql Server Management Studio 2008没有编写表权限的脚本。这是一个错误还是有其他方法可以解决?它正在为存储过程创建权限,但不是为表创建。我是系统管理员。
如果无法解决,是否有免费的sql server工具可用于脚本化权限?
我在 SQL Server 论坛中发现了这个方便的脚本,但我再也找不到它了:
CREATE VIEW [dbo].[viw_DBPerms] AS
SELECT
CASE
WHEN o.type = 'P' THEN 'Stored Procedure'
WHEN o.type = 'TF' THEN 'Table Function'
WHEN o.type = 'FN' THEN 'Scalar Function'
WHEN o.type = 'U' THEN 'Table'
WHEN o.type = 'V' THEN 'View'
WHEN o.type = 'SQ' THEN 'Service Queue'
ELSE o.type
END AS [Type],
s.name AS [Schema],
o.name AS [Object],
pr.name AS [User],
pe.permission_name AS Permission
FROM sys.database_permissions pe
LEFT JOIN sys.database_principals pr ON pe.grantee_principal_id = pr.principal_id
JOIN
( SELECT [object_id] AS [id], [name], type, schema_id, 1 AS [class] FROM sys.objects
UNION
SELECT [service_id] AS [id], [name] COLLATE SQL_Latin1_General_CP1_CI_AS [name], 'Service', '0', 17 AS [class] FROM sys.services
UNION
SELECT [service_cONtract_id] AS [id], [name], 'Service Contract', '0', 16 AS [class] FROM sys.service_contracts
UNION
SELECT [message_type_id] AS [id], [name], 'Message Type', '0', 15 AS [class] FROM sys.service_message_types
) o
ON pe.major_id = o.id AND pe.class = o.class
LEFT JOIN sys.schemas s ON o.schema_id = s.schema_id
如果只需要使用,则可以使用以下方法:
SELECT Object,
SUM(CASE Permission WHEN 'SELECT' THEN 1 ELSE 0 END) AS 'SELECT',
SUM(CASE Permission WHEN 'INSERT' THEN 1 ELSE 0 END) AS 'INSERT',
SUM(CASE Permission WHEN 'UPDATE' THEN 1 ELSE 0 END) AS 'UPDATE',
SUM(CASE Permission WHEN 'DELETE' THEN 1 ELSE 0 END) AS 'DELETE',
SUM(CASE Permission WHEN 'ALTER' THEN 1 ELSE 0 END) AS 'ALTER'
FROM viw_DBPerms
WHERE [User] = '<sqluser>'
GROUP BY Object
这在SQL 2005上可行,我认为在SQL 2008上也应该能正常工作。