blocks|key|871885|text|您可以使用下面这样的脚本获得所需的内容：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|871886|declare+@RoleName+varchar(50)+=+'RoleName'

declare+@Script+varchar(max)+=+'CREATE+ROLE+'+%2B+@RoleName+%2B+char(13)
select+@script+=+@script+%2B+'GRANT+'+%2B+prm.permission_name+%2B+'+ON+'+%2B+OBJECT_NAME(major_id)+%2B+'+TO+'+%2B+rol.name+%2B+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|code-block|syntax|javascript|871887|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can get what you need with a script like this:

<pre><code>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
</code></pre>

blocks|key|654642|text|这很痛苦，但您要查找的所有内容都在几个系统视图中:+sys.database_permissions、sys.database_principals和sys.database_role_members。它不是一种享受的原因是因为sys.database_permissions中的major_id和minor_id根据类列的不同具有不同的含义。但如果您的权限相对简单，那么这可能不是那么糟糕。看一看，看看你能得到什么。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|654643|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|654670|text|我在上面的代码中添加了一个额外的脚本功能。select脚本列出了角色和权限，因此您只需执行结果：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|654671|--+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+['+%2B++DB_NAME()+%2B+']+CREATE+ROLE+['+%2B+@RoleName+%2B+']'+AS+'Create+Role'
SELECT+'USE+['+%2B++DB_NAME()+%2B+']+GRANT+'+%2B+[Permission]+%2B+'+ON+SCHEMA::['+%2B+[GrantedBy]+%2B+']+TO+['+%2B+@RoleName+%2B+']'+AS+'Add+Permissions'+
FROM+@RoleTable+|code-block|syntax|javascript|654672|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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:

<pre><code>-- 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 
</code></pre>

blocks|key|654721|text|此脚本为您的角色生成GRANT语句。我喜欢，它支持列leven权限。它必须适应您的需求(即：改进用于更复杂数据库，连接语句和执行，包括您角色的create语句)。但我想给你一个概念：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|654722|SELECT+'GRANT+'+%2B+database_permissions.permission_name+%2B+'+ON+'+%2B
++++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)+%2B+'+('%2B+name+%2B+')'
++++++++++++++++++FROM+sys.columns+
++++++++++++++++++WHERE+object_id+=+database_permissions.major_id+
++++++++++++++++++AND+column_id+=+database_permissions.minor_id)+end
++++++++ELSE+'other'+
++++END+%2B+
++++'+TO+'+%2B+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')|code-block|syntax|javascript|654723|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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:

<pre><code>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 &gt; 0
AND permission_name in ('SELECT','INSERT','UPDATE','DELETE')
</code></pre>

blocks|key|872093|text|我详述了Mario+Eis的回答：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|872094|SELECT+'GRANT+'+%2B+database_permissions.permission_name+%2B+'+ON+'+%2B+CASE+database_permissions.class_desc
++++++++WHEN+'SCHEMA'
++++++++++++THEN+'['+%2B+schema_name(major_id)+%2B+']'
++++++++WHEN+'OBJECT_OR_COLUMN'
++++++++++++THEN+CASE+
++++++++++++++++++++WHEN+minor_id+=+0
++++++++++++++++++++++++THEN'['%2BOBJECT_SCHEMA_NAME(major_id)+%2B+'].'+%2B+'['+%2B+object_name(major_id)+%2B+']'+COLLATE+Latin1_General_CI_AS_KS_WS
++++++++++++++++++++ELSE+(
++++++++++++++++++++++++++++SELECT+object_name(object_id)+%2B+'+('+%2B+NAME+%2B+')'
++++++++++++++++++++++++++++FROM+sys.columns
++++++++++++++++++++++++++++WHERE+object_id+=+database_permissions.major_id
++++++++++++++++++++++++++++++++AND+column_id+=+database_permissions.minor_id
++++++++++++++++++++++++++++)
++++++++++++++++++++END
++++++++ELSE+'other'
++++++++END+%2B+'+TO+['+%2B+database_principals.NAME+%2B+']'+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'
++++++++)|code-block|syntax|javascript|872095|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I expanded on Mario Eis's answer:

<pre><code>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 &gt; 0
 AND permission_name IN (
 'SELECT'
 ,'INSERT'
 ,'UPDATE'
 ,'DELETE'
 ,'EXECUTE'
 )
</code></pre>

blocks|key|872181|text|我已经做了一个相当全面的脚本，它不仅写出了所有的权限，而且还写出了所有的成员资格，并在蛋糕格式上添加了糖霜，以便轻松地复制/粘贴到新的查询窗口中。我已经将这个脚本发布到了my+blog上，并不时地更新它，但以下是应该涵盖大多数基础的当前版本：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|872182|/********************************************************************
+*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*
+*+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)+%2B+CHAR(10)
SET+@roleDesc+=+'CREATE+ROLE+['+%2B+@roleName+%2B+']'+%2B+@crlf+%2B+'GO'+%2B+@crlf+%2B+@crlf

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

--+Display+users+within+Role.++Code+stubbed+by+Joe+Spivey
SELECT++@roleDesc+=+@roleDesc+%2B+'EXECUTE+sp_AddRoleMember+'''+%2B+roles.name+%2B+''',+'''+%2B+users.name+%2B+''''+%2B+@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)+%2B+CHAR(13),+REVERSE(SUBSTRING(@roleDesc,+0,+8000)))

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

PRINT+@RoleDesc+%2B+'GO'|code-block|syntax|javascript|872183|值得注意的是，您可能会遇到这样一种情况:+sp_AddRoleMember+system+sp将用户添加到以前不存在的数据库中。在这种情况下，即使添加了用户，他们也不会被授予CONNECT权限，并且该用户或组进行的任何连接尝试都将生成用户登录错误。要解决此问题，您需要对数据库中的每个新用户/组执行以下命令：|872184|USE+[DatabaseName]
GO
GRANT+CONNECT+TO+[Login/GroupName]
GO|872185|entityMap|0|LINK|mutability|MUTABLE|url|https://dbaeyes.wordpress.com/2013/04/19/fully-script-out-a-mssql-database-role/^0|2D|7|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@$A|V|B|W|1|X]]|C|$]]|$1|D|3|E|5|F|7|Y|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|C|$]]|$1|K|3|L|5|F|7|10|8|@]|9|@]|C|$G|H]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|C|$]]]|N|$O|$5|P|Q|R|C|$S|T]]]]

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 <a href="https://dbaeyes.wordpress.com/2013/04/19/fully-script-out-a-mssql-database-role/" rel="noreferrer">my blog</a> and update it from time-to-time, but below is the current version which should cover most bases:

<pre><code>/********************************************************************
 * *
 * 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 &lt; 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) &gt; 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) &gt; 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'
</code></pre>

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:

<pre><code>USE [DatabaseName]
GO
GRANT CONNECT TO [Login/GroupName]
GO
</code></pre>

blocks|key|654871|text|--+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+['+%2B+@RoleName+%2B+'];'%2B+char(13)

SELECT++'GRANT+'+%2B+prm.permission_name+%2B+'+ON+['+%2B+
OBJECT_NAME(major_id)+%2B+']+TO+['+%2B+rol.name+%2B+']+;'+%2B+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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|654872|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>-- 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
</code></pre>

I need to make a script to copy one particular database role from one SQL server to another.

Is there an easy way to generate a script that creates the role and all the role permissions?

How to script SQL server database role?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我需要创建一个脚本来将一个特定的数据库角色从一个SQL服务器复制到另一个。有没有一种简单的方法来生成创建角色和所有角色权限的脚本？

问如何编写SQL server数据库角色脚本？
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何编写SQL server数据库角色脚本？EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何编写SQL server数据库角色脚本？
EN