36 lines
799 B
Transact-SQL
36 lines
799 B
Transact-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
|
|
|