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?
可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):
问题:
回答1:
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
回答2:
I expanded on Mario Eis's answer:
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'
)
回答3:
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:
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')
回答4:
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.
Visual Studio with 'Database Drvelopment' option and Red Gate SQL Compare can generate the change script between to databases, this includes users, roles and role membership.
Generated by VS role membership looks like this:
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.
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.
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.
回答5:
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 blog and 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:
USE [DatabaseName]
GO
GRANT CONNECT TO [Login/GroupName]
GO
回答6:
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.
回答7:
-- 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
回答8:
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
{var%20f='http://v.t.sina.com.cn/share/share.php?appkey=1515056452',u=z||d.location,p=['&url=',e(u),'&title=',e(t||d.title),'&source=',e(r),'&sourceUrl=',e(l),'&content=',c||'gb2312','&pic=',e(p||'')].join('');function%20a(){if(!window.open([f,p].join(''),'mb',['toolbar=0,status=0,resizable=1,width=440,height=430,left=',(s.width-440)/2,',top=',(s.height-430)/2].join('')))u.href=[f,p].join('');};if(/Firefox/.test(navigator.userAgent))setTimeout(a,0);else%20a();})(screen,document,encodeURIComponent,'','','https://img.manongdao.com/sparkler-677774__340.jpg', '推荐 放荡不羁爱自由 的文章《How to script SQL server database role?》','https://www.manongdao.com/article-549314.html','页面编码gb2312|utf-8默认gb2312'));){kind=link}