Sql Server Management Studio 2008没有脚本表权限

Sql Server Management Studio 2008 is not scripting table permissions even when I select the option to script object level permissions. Is this a bug or is there another way to do this? It is creating permissions for stored procs, but not for tables. I am a sysadmin.

即使我选择脚本对象级别权限的选项,Sql Server Management Studio 2008也不是脚本表权限。这是一个错误还是有另一种方法可以做到这一点?它正在为存储过程创建权限,但不为表创建权限。我是一个系统管理员。

If it doesn't work, are there free sql server tools to script the permissions?

如果它不起作用,是否有免费的SQL Server工具来编写权限脚本?

1 个解决方案

#1

I came across this handy script on one of the SQL Server forum sites but I'm buggered if I can find it again:

我在其中一个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

...then just use:

...然后只使用:

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

This works on SQL 2005 and I would expect it to work fine on SQL 2008.

这适用于SQL 2005,我希望它在SQL 2008上运行良好。

#1