如何编写SQL Server数据库角色脚本？

Question

如何编写SQL Server数据库角色脚本？

sqlsql-servert-sqlsql-server-2008

26

我需要编写一个脚本将一个特定的数据库角色从一个SQL服务器复制到另一个SQL服务器。

是否有一种简便的方法来生成一个创建该角色及其所有权限的脚本？

- Foster Geng

8个回答

15

我编写了一份相当全面的脚本，不仅可以列出所有权限，还可以列出所有成员，并且为了方便复制/粘贴到新的查询窗口中，格式化输出。我将脚本发布在我的博客上，并会定期更新。以下是当前版本，应该覆盖大部分情况:

/********************************************************************
 *                                                                  *
 * Author: John Eisbrener                                           *
 * Script Purpose: Script out Database Role Definition              *
 * Notes: Please report any bugs to http://www.dbaeyes.com/         *
 *                                                                  *
 ********************************************************************/
DECLARE @roleName VARCHAR(255)
SET @roleName = 'DatabaseRoleName'

-- Script out the Role
DECLARE @roleDesc VARCHAR(MAX), @crlf VARCHAR(2)
SET @crlf = CHAR(13) + CHAR(10)
SET @roleDesc = 'CREATE ROLE [' + @roleName + ']' + @crlf + 'GO' + @crlf + @crlf

SELECT    @roleDesc = @roleDesc +
        CASE dp.state
            WHEN 'D' THEN 'DENY '
            WHEN 'G' THEN 'GRANT '
            WHEN 'R' THEN 'REVOKE '
            WHEN 'W' THEN 'GRANT '
        END + 
        dp.permission_name + ' ' +
        CASE dp.class
            WHEN 0 THEN ''
            WHEN 1 THEN --table or column subset on the table
                CASE WHEN dp.major_id < 0 THEN
                    + 'ON [sys].[' + OBJECT_NAME(dp.major_id) + '] '
                ELSE
                    + 'ON [' +
                    (SELECT SCHEMA_NAME(schema_id) + '].[' + name FROM sys.objects WHERE object_id = dp.major_id)
                        + -- optionally concatenate column names
                    CASE WHEN MAX(dp.minor_id) > 0 
                         THEN '] ([' + REPLACE(
                                        (SELECT name + '], [' 
                                         FROM sys.columns 
                                         WHERE object_id = dp.major_id 
                                            AND column_id IN (SELECT minor_id 
                                                              FROM sys.database_permissions 
                                                              WHERE major_id = dp.major_id
                                                                AND USER_NAME(grantee_principal_id) IN (@roleName)
                                                             )
                                         FOR XML PATH('')
                                        ) --replace final square bracket pair
                                    + '])', ', []', '')
                         ELSE ']'
                    END + ' '
                END
            WHEN 3 THEN 'ON SCHEMA::[' + SCHEMA_NAME(dp.major_id) + '] '
            WHEN 4 THEN 'ON ' + (SELECT RIGHT(type_desc, 4) + '::[' + name FROM sys.database_principals WHERE principal_id = dp.major_id) + '] '
            WHEN 5 THEN 'ON ASSEMBLY::[' + (SELECT name FROM sys.assemblies WHERE assembly_id = dp.major_id) + '] '
            WHEN 6 THEN 'ON TYPE::[' + (SELECT name FROM sys.types WHERE user_type_id = dp.major_id) + '] '
            WHEN 10 THEN 'ON XML SCHEMA COLLECTION::[' + (SELECT SCHEMA_NAME(schema_id) + '.' + name FROM sys.xml_schema_collections WHERE xml_collection_id = dp.major_id) + '] '
            WHEN 15 THEN 'ON MESSAGE TYPE::[' + (SELECT name FROM sys.service_message_types WHERE message_type_id = dp.major_id) + '] '
            WHEN 16 THEN 'ON CONTRACT::[' + (SELECT name FROM sys.service_contracts WHERE service_contract_id = dp.major_id) + '] '
            WHEN 17 THEN 'ON SERVICE::[' + (SELECT name FROM sys.services WHERE service_id = dp.major_id) + '] '
            WHEN 18 THEN 'ON REMOTE SERVICE BINDING::[' + (SELECT name FROM sys.remote_service_bindings WHERE remote_service_binding_id = dp.major_id) + '] '
            WHEN 19 THEN 'ON ROUTE::[' + (SELECT name FROM sys.routes WHERE route_id = dp.major_id) + '] '
            WHEN 23 THEN 'ON FULLTEXT CATALOG::[' + (SELECT name FROM sys.fulltext_catalogs WHERE fulltext_catalog_id = dp.major_id) + '] '
            WHEN 24 THEN 'ON SYMMETRIC KEY::[' + (SELECT name FROM sys.symmetric_keys WHERE symmetric_key_id = dp.major_id) + '] '
            WHEN 25 THEN 'ON CERTIFICATE::[' + (SELECT name FROM sys.certificates WHERE certificate_id = dp.major_id) + '] '
            WHEN 26 THEN 'ON ASYMMETRIC KEY::[' + (SELECT name FROM sys.asymmetric_keys WHERE asymmetric_key_id = dp.major_id) + '] '
         END COLLATE SQL_Latin1_General_CP1_CI_AS
         + 'TO [' + @roleName + ']' + 
         CASE dp.state WHEN 'W' THEN ' WITH GRANT OPTION' ELSE '' END + @crlf
FROM    sys.database_permissions dp
WHERE    USER_NAME(dp.grantee_principal_id) IN (@roleName)
GROUP BY dp.state, dp.major_id, dp.permission_name, dp.class

SELECT @roleDesc = @roleDesc + 'GO' + @crlf + @crlf

-- Display users within Role.  Code stubbed by Joe Spivey
SELECT  @roleDesc = @roleDesc + 'EXECUTE sp_AddRoleMember ''' + roles.name + ''', ''' + users.name + '''' + @crlf
FROM    sys.database_principals users
        INNER JOIN sys.database_role_members link 
            ON link.member_principal_id = users.principal_id
        INNER JOIN sys.database_principals roles 
            ON roles.principal_id = link.role_principal_id
WHERE   roles.name = @roleName

-- PRINT out in blocks of up to 8000 based on last \r\n
DECLARE @printCur INT
SET @printCur = 8000

WHILE LEN(@roleDesc) > 8000
BEGIN
    -- Reverse first 8000 characters and look for first lf cr (reversed crlf) as delimiter
    SET @printCur = 8000 - CHARINDEX(CHAR(10) + CHAR(13), REVERSE(SUBSTRING(@roleDesc, 0, 8000)))

    PRINT LEFT(@roleDesc, @printCur)
    SELECT @roleDesc = RIGHT(@roleDesc, LEN(@roleDesc) - @printCur)
END

PRINT @RoleDesc + 'GO'

需要注意的是，您可能会遇到这样一种情况，即 sp_AddRoleMember 系统存储过程会将之前不存在于数据库中的用户添加到数据库中。但在这种情况下，即使用户已被添加，他们也没有被授予 CONNECT 权限，因此任何该用户或组尝试进行连接的操作都将导致用户登录错误。要解决此问题，您需要对每个新用户/组在数据库中执行以下操作：

USE [DatabaseName]
GO
GRANT CONNECT TO [Login/GroupName]
GO

- John Eisbrener

8

我在 Mario Eis 的答案上进行了扩展：

SELECT 'GRANT ' + database_permissions.permission_name + ' ON ' + CASE database_permissions.class_desc
        WHEN 'SCHEMA'
            THEN '[' + schema_name(major_id) + ']'
        WHEN 'OBJECT_OR_COLUMN'
            THEN CASE 
                    WHEN minor_id = 0
                        THEN'['+OBJECT_SCHEMA_NAME(major_id) + '].' + '[' + object_name(major_id) + ']' COLLATE Latin1_General_CI_AS_KS_WS
                    ELSE (
                            SELECT object_name(object_id) + ' (' + NAME + ')'
                            FROM sys.columns
                            WHERE object_id = database_permissions.major_id
                                AND column_id = database_permissions.minor_id
                            )
                    END
        ELSE 'other'
        END + ' TO [' + database_principals.NAME + ']' COLLATE Latin1_General_CI_AS_KS_WS
FROM sys.database_permissions
JOIN sys.database_principals ON database_permissions.grantee_principal_id = database_principals.principal_id
LEFT JOIN sys.objects --left because it is possible that it is a schema
    ON objects.object_id = database_permissions.major_id
WHERE database_permissions.major_id > 0
    AND permission_name IN (
        'SELECT'
        ,'INSERT'
        ,'UPDATE'
        ,'DELETE'
        ,'EXECUTE'
        )

- mcfea

3

谢谢，我使用了这个选项。由于我只需要在特定角色上使用它，所以我将这个条件加到了您的脚本的WHERE子句的末尾：AND database_principals.NAME = 'RoleNameHere'。 - David McClelland

2

如果在SELECT子句的末尾添加“AS PermStatement”，并在WHERE子句之后添加“ORDER BY PermStatement”，则结果将按字母顺序排序。这不会影响功能，但如果您想比较同一数据库的两个不同版本上角色的权限，则会有所帮助。 - Night Owl

如果您想修改一个尚不存在的特定角色，请不要忘记先创建该角色：CREATE ROLE [rolename]。 - Heinrich Ulbricht

5

这个脚本可以为你的角色生成GRANT语句。我喜欢它支持列级权限。但是，它需要根据你的需求进行适应（例如，针对更复杂的数据库进行改进，连接语句并执行，为你的角色包括创建语句）。以下只是给您提供了一个想法：

SELECT 'GRANT ' + database_permissions.permission_name + ' ON ' +
    CASE database_permissions.class_desc
        WHEN 'SCHEMA' THEN schema_name(major_id)
        WHEN 'OBJECT_OR_COLUMN' THEN
            CASE WHEN minor_id = 0 THEN object_name(major_id) COLLATE Latin1_General_CI_AS_KS_WS
            ELSE (SELECT object_name(object_id) + ' ('+ name + ')'
                  FROM sys.columns 
                  WHERE object_id = database_permissions.major_id 
                  AND column_id = database_permissions.minor_id) end
        ELSE 'other' 
    END + 
    ' TO ' + database_principals.name COLLATE Latin1_General_CI_AS_KS_WS
FROM sys.database_permissions
JOIN sys.database_principals
ON database_permissions.grantee_principal_id = database_principals.principal_id
LEFT JOIN sys.objects --left because it is possible that it is a schema
ON objects.object_id = database_permissions.major_id
WHERE database_permissions.major_id > 0
AND permission_name in ('SELECT','INSERT','UPDATE','DELETE')

- Mario Eis

有没有办法提取模式，以便您可以获得“GRANT SELECT ON test.TABLE TO fooUser”？ - mcfea

我在我的回答中添加了模式。 - mcfea

3

在SSMS中右键单击用户/登录/角色节点，选择“Script As”将会脚本化特定的用户/登录/角色。然而，你无法通过这种方式脚本化角色成员资格。

Visual Studio与“数据库开发”选项以及Red Gate SQL Compare可以生成两个数据库之间的更改脚本，其中包括用户、角色和角色成员资格。

由VS生成的角色成员资格如下：

EXECUTE sp_addrolemember @rolename = N'db_datareader', @membername = N'DOMAIN\User';

如果你没有VS，你可以手动编写这些内容，或者创建一个用于生成它们的SQL脚本。

我相信也应该有一个免费的工具来做类似的事情，但是由于我不需要它，因为我有Visual Studio，所以我从来没有去找过。

编辑：我刚意识到我回答了一个错误的问题，你在问角色权限，而我在告诉你关于角色成员资格。抱歉，如果这对其他人有用，我会把这个答案留在这里。 Alex Aza的答案看起来很好。

- Andrew Savinykh

1

当我右击角色并选择'Script As'时，它只生成一行CREATE ROLE [RoleName] AUTHORIZATION [dbo]。我还需要获得角色权限。 - Foster Geng

@Foster Geng：是的，我知道。我个人使用Visual Studio来处理这种类型的工作。它会生成像EXECUTE sp_addrolemember @rolename = N'db_datareader', @membername = N'DOMAIN\User';这样的代码。 - Andrew Savinykh

1

虽然有点麻烦，但你所需要的一切都在几个系统视图中：sys.database_permissions、sys.database_principals和sys.database_role_members。之所以不是那么容易，是因为sys.database_permissions中的major_id和minor_id根据class列具有不同的含义。但如果你的权限相对简单，那么这可能并不那么糟糕。看看它，看看你能得到什么。

- Ben Thul

1

-- Use this if you have a lot of permissions assigned to a Database Role
-- Before running, set results to Text

SET NOCOUNT ON

Use MyDB;  -- CHANGE DATABASE NAME

DECLARE @RoleName varchar(50) = 'sp_exec' --- change role name here

SELECT 'CREATE ROLE [' + @RoleName + '];'+ char(13)

SELECT  'GRANT ' + prm.permission_name + ' ON [' + 
OBJECT_NAME(major_id) + '] TO [' + rol.name + '] ;' + char(13) COLLATE Latin1_General_CI_AS

from sys.database_permissions prm

    join sys.database_principals rol on

        prm.grantee_principal_id = rol.principal_id

where rol.name = @RoleName

- MudassiR ShaikH

欢迎来到Stack Overflow！感谢您提供这段代码片段，它可能会提供一些有限的、即时的帮助。一个适当的解释将极大地提高其长期价值，通过展示为什么这是一个好的解决方案来使其对未来读者更有用，他们可能有其他类似的问题。请编辑您的答案以添加一些解释，包括您所做的假设。 - common sense

0

我在上面的代码中添加了一个额外的脚本功能。选择脚本会筛选出角色和权限，因此您只需要执行结果：

-- Update the RoleName with the name of your role
DECLARE @RoleName VARCHAR(75) = 'RoleName'

DECLARE @RoleTable TABLE ([GrantedBy] VARCHAR (50) NOT NULL, [Permission] VARCHAR (50) NOT NULL, [State] VARCHAR (50) NOT NULL)
DECLARE @RoleScript VARCHAR(75)

INSERT INTO @RoleTable SELECT p2.[name], dbp.[permission_name], dbp.[state_desc] 
FROM [sys].[database_permissions] dbp LEFT JOIN [sys].[objects] so
    ON dbp.[major_id] = so.[object_id] LEFT JOIN [sys].[database_principals] p
    ON dbp.[grantee_principal_id] = p.[principal_id] LEFT JOIN [sys].[database_principals] p2
    ON dbp.[grantor_principal_id] = p2.[principal_id]
WHERE p.[name] = @RoleName

SELECT 'USE [' +  DB_NAME() + '] CREATE ROLE [' + @RoleName + ']' AS 'Create Role'
SELECT 'USE [' +  DB_NAME() + '] GRANT ' + [Permission] + ' ON SCHEMA::[' + [GrantedBy] + '] TO [' + @RoleName + ']' AS 'Add Permissions' 
FROM @RoleTable

- Tim Debisz

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Alex Aza · Accepted Answer

您可以使用以下脚本获取所需内容：

declare @RoleName varchar(50) = 'RoleName'

declare @Script varchar(max) = 'CREATE ROLE ' + @RoleName + char(13)
select @script = @script + 'GRANT ' + prm.permission_name + ' ON ' + OBJECT_NAME(major_id) + ' TO ' + rol.name + char(13) COLLATE Latin1_General_CI_AS 
from sys.database_permissions prm
    join sys.database_principals rol on
        prm.grantee_principal_id = rol.principal_id
where rol.name = @RoleName

print @script