SQL Server – Script Out a Database Role and Securables

Sometimes, as a DBA, you will have to copy a database role to another instance, such as when you have to move something from development to testing or production. Recently, I discovered that using the SSMS “script as” option doesn’t help very much at all. It simply generates a statement to create the role but ignores all of the securables that the role has. So what you’d end up with, if you executed that script, is a role that did nothing. A lot of good that does…

After searching online for it, I couldn’t believe that such a simple script was not available. If it was then I sure wasn’t finding it.

Anyway, this script generates a script that adds a role to a database and then adds the associated securables. It consists only of SELECT statements and poses no risk of altering any data. The script that this script generates will only replicate what already existed.

Link to formatted code: https://gitea.rnsworth.com/ben/PublicPosts/src/commit/5e57dabcf4fba63101c7f39bc68b0ec711944f73/Script%20Out%20Role.sql

/*----====----====----====----====----====----====----====----====

* Generate script to "script out" a role on a database.
* For Best Results, set
* RESULTS TO TEXT
* Press Ctrl+Shift+m to fill in variables

----====----====----====----====----====----====----====----====*/

SET NOCOUNT ON
GO

SELECT 'USE <DatabaseName,string,Database Name>'
SELECT 'GO'
GO

SELECT
'EXEC dbo.sp_addrole @rolename = N''<RoleName,string,Role Name>'', @ownername = N''dbo''
GO
'

USE <DatabaseName,string,Database Name>
GO

 

SELECT 'GRANT ' + permission_name + ' ON ' + OBJECT_NAME(major_id) + ' TO <RoleName,string,Role Name>'
FROM sys.database_permissions
WHERE grantee_principal_id in
(
SELECT principal_id FROM sys.database_principals
WHERE TYPE = 'R' and name = '<RoleName,string,Role Name>'
)
AND class > 0

Comment

Thanx for the query.But we may have different users than dbo. Also permission can be different too….

SET NOCOUNT ON
GO
DECLARE
@cmd NVARCHAR(MAX)
,@loop INT
,@role_cnt INT
,@role_name VARCHAR(100)
,@psn VARCHAR(1000)

DECLARE @roles TABLE
(
id INT IDENTITY,
role_name VARCHAR(100),
op_id INT
)

INSERT @roles(role_name,op_id)

SELECT name,owning_principal_id FROM sys.database_principals
WHERE TYPE=’R’ AND is_fixed_role = 0 AND name ‘public’

SELECT @loop =1,@role_cnt = COUNT(*)+1 FROM @roles

WHILE(@loop 0

SET @loop = @loop + 1
print @cmd
END

Comment

Leave a Reply Cancel reply