如何编写 SQL 服务器数据库角色的脚本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6300740/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to script SQL server database role?
提问by Foster Geng
I need to make a script to copy one particular database role from one SQL server to another.
我需要编写一个脚本来将一个特定的数据库角色从一个 SQL 服务器复制到另一个。
Is there an easy way to generate a script that creates the role and all the role permissions?
是否有一种简单的方法来生成创建角色和所有角色权限的脚本？
回答by Alex Aza
You can get what you need with a script like this:
您可以使用这样的脚本获得所需的内容：
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
回答by mcfea
I expanded on Mario Eis's answer:
我扩展了 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'
)
回答by John Eisbrener
I've made a pretty comprehensive script that not only scripts out all the permissions, but also all membership, and to put frosting on the cake formats the output for easy copy/pasting into a new query window. I've posted the script to my blogand update it from time-to-time, but below is the current version which should cover most bases:
我制作了一个非常全面的脚本，它不仅编写了所有权限，还编写了所有成员资格，并在蛋糕上添加了糖霜格式，以便轻松复制/粘贴到新的查询窗口中。我已将脚本发布到我的博客并不时更新它，但以下是当前版本，应涵盖大多数基础：
/********************************************************************
* *
* 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'
Of note, you may run into a situation where the sp_AddRoleMember system sp adds user(s) to the DB that wasn't previously there. In this case, even though the user(s) is added, they are NOT granted the CONNECT permission, and any connection attempt made by said user or group will generate a user login error. To rectify this issue, you need to execute the following per new user/group within the db:
值得注意的是，您可能会遇到 sp_AddRoleMember 系统 sp 将用户添加到以前不存在的数据库的情况。在这种情况下，即使添加了用户，他们也没有被授予 CONNECT 权限，并且该用户或组进行的任何连接尝试都会产生用户登录错误。要解决此问题，您需要在 db 中为每个新用户/组执行以下操作：
USE [DatabaseName]
GO
GRANT CONNECT TO [Login/GroupName]
GO
回答by Mario Eis
This script generates GRANT statements for your roles. I like, that it supports column leven permissions. It has to be adapted to your need (ie. improve for more complex databases, concatenate statements and execute, include create statement for your role). But just to give you an idea:
此脚本为您的角色生成 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')
回答by Andrew Savinykh
IN SSMS right clicking user/login/role node and selecting 'Script As' will script this particular user / login / role. You can't script role mebership this way though.
在 SSMS 中右键单击用户/登录名/角色节点并选择“脚本为”将编写此特定用户/登录名/角色的脚本。但是，您不能以这种方式编写角色成员身份。
Visual Studiowith 'Database Drvelopment' option and Red Gate SQL Comparecan generate the change script between to databases, this includes users, roles and role membership.
带有“Database Drvelopment”选项和Red Gate SQL Compare 的Visual Studio可以生成数据库之间的更改脚本，这包括用户、角色和角色成员资格。
Generated by VS role membership looks like this:
由 VS 角色成员生成如下所示：
EXECUTE sp_addrolemember @rolename = N'db_datareader', @membername = N'DOMAIN\User';
If you don't have VS, you can either write those manually, or create a sql script for generating them.
如果你没有 VS，你可以手动编写它们，或者创建一个 sql 脚本来生成它们。
I'm sure there also should be a free tool to do something like this, but since I don't need it as I have Visual Studio, I never looked for it.
我确信也应该有一个免费工具来做这样的事情，但由于我不需要它，因为我有 Visual Studio，所以我从未寻找过它。
Edit: I just realized that I'm answering a wrong question, you are asking about role permission and I'm telling you about role membership. Sorry about this. I'll leave this answer here in case it can be useful to someone else. Answer by Alex Aza looks good.
编辑：我刚刚意识到我回答了一个错误的问题，您是在询问角色权限，而我在告诉您角色成员资格。为此事道歉。我会在这里留下这个答案，以防它对其他人有用。Alex Aza 的回答看起来不错。
回答by Ben Thul
It's a pain, but everything you're looking for is in a couple of system views: sys.database_permissions, sys.database_principals, and sys.database_role_members. The reason it isn't a treat is because the major_id and minor_id in sys.database_permissions have different meanings based on the class column. But if your permissions are relatively simple, then this might not be so bad. Give it a look and see what you can get.
这很痛苦，但您正在寻找的一切都在几个系统视图中：sys.database_permissions、sys.database_principals 和 sys.database_role_members。它不是一种享受的原因是因为 sys.database_permissions 中的 major_id 和 minor_id 基于类列具有不同的含义。但如果您的权限相对简单，那么这可能还不错。看看它，看看你能得到什么。
回答by MudassiR ShaikH
-- 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
回答by Tim Debisz
I added a additional script feature to the code above. The select scripts out the role and permissions so you just need to execute the result:
我在上面的代码中添加了一个额外的脚本功能。选择脚本输出角色和权限，因此您只需要执行结果：
-- 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
