Compile a script to generate custom roles and permissions

Original posted in http://community.csdn.net/Expert/topic/4293/4293496.xml? Temp =. 623852.

/*

Adapted from an article on a foreign site, the original address cannot be clearly remembered

-- Vivianfdlpw 2005.9 reference please keep this information

*/

Declare @ databaserolename [sysname]
Set @ databaserolename = 'createrole' -- role name

Set nocount on
Declare
@ Errstatement [varchar] (8000 ),
@ Msgstatement [varchar] (8000 ),
@ Databaseroleid [smallint],
@ Isapplicationrole [bit],
@ Objectid [int],
@ Objectname [sysname]

Select
@ Databaseroleid = [uid],
@ Isapplicationrole = cast ([isapprole] As bit)
From [DBO]. [sysusers]
Where
[Name] = @ databaserolename
And
(
[Issqlrole] = 1
Or [isapprole] = 1
)
And [name] Not in
(
'Public ',
'Information _ scheme ',
'Db _ owner ',
'Db _ accessadmin ',
'Db _ securityadmin ',
'Db _ ddladmin ',
'Db _ backupoperator ',
'Db _ datareader ',
'Db _ datawriter ',
'Db _ denydatareader ',
'Db _ denydatawriter'
)

If @ databaseroleid is null
Begin
If @ databaserolename in
(
'Public ',
'Information _ scheme ',
'Db _ owner ',
'Db _ accessadmin ',
'Db _ securityadmin ',
'Db _ ddladmin ',
'Db _ backupoperator ',
'Db _ datareader ',
'Db _ datawriter ',
'Db _ denydatareader ',
'Db _ denydatawriter'
)
Set @ errstatement = 'role' + @ databaserolename + 'is a fixed database role and cannot be scripted .'
Else
Set @ errstatement = 'role' + @ databaserolename + 'does not exist in' + db_name () + '.' + char (13) +
'Please provide the name of a current role in '+ db_name () + 'You wish to script .'

Raiserror (@ errstatement, 16, 1)
End
Else
Begin
Set @ msgstatement = '-- Security creation script for role' + @ databaserolename + char (13) +
'-- Created at:' + convert (varchar, getdate (), 112) + Replace (convert (varchar, getdate (), 108 ),':','') + char (13) +
'-- Created by:' + 'ldyia '+ char (13) +
'-- Add role to database' + char (13)
If @ isapplicationrole = 1
Set @ msgstatement = @ msgstatement + 'exec sp_addapprole' + char (13) +
Char (9) + '@ rolename = ''' + @ databaserolename + ''' + char (13) +
Char (9) + '@ Password = ''{please provide the password here} ''' + char (13)
Else
Begin
Set @ msgstatement = @ msgstatement + 'exec sp_addrole' + char (13) +
Char (9) + '@ rolename ''' + @ databaserolename + ''' + char (13)
Print 'Go'
End
Set @ msgstatement = @ msgstatement + '-- set object specific permissions for role'
Print @ msgstatement

Select
Distinct ([sysobjects]. [ID]),
'[' + User_name ([sysobjects]. [uid]) + ']. [' + [sysobjects]. [name] + ']' As 'object'
Into #
From [DBO]. [sysprotects]
Left join [DBO]. [sysobjects]
On [sysprotects]. [ID] = [sysobjects]. [ID]
Where [sysobjects]. [ID] is not null
And [sysprotects]. [uid] = @ databaseroleid

Insert # select 0, @ databaserolename

Declare _ sysobjects
Cursor
Local
Forward_only
Read_only
For
Select * from #

Open _ sysobjects
Fetch
Next
From _ sysobjects
Into
@ Objectid,
@ Objectname
While @ fetch_status = 0
Begin
Set @ msgstatement =''
If exists (select * from [DBO]. [sysprotects] Where [ID] = @ objectid and [uid] = @ databaseroleid and [action] = 193 and [protecttype] = 205)
Set @ msgstatement = @ msgstatement + 'select ,'
If exists (select * from [DBO]. [sysprotects] Where [ID] = @ objectid and [uid] = @ databaseroleid and [action] = 195 and [protecttype] = 205)
Set @ msgstatement = @ msgstatement + 'insert ,'
If exists (select * from [DBO]. [sysprotects] Where [ID] = @ objectid and [uid] = @ databaseroleid and [action] = 197 and [protecttype] = 205)
Set @ msgstatement = @ msgstatement + 'Update ,'
If exists (select * from [DBO]. [sysprotects] Where [ID] = @ objectid and [uid] = @ databaseroleid and [action] = 196 and [protecttype] = 205)
Set @ msgstatement = @ msgstatement + 'delete ,'
If exists (select * from [DBO]. [sysprotects] Where [ID] = @ objectid and [uid] = @ databaseroleid and [action] = 224 and [protecttype] = 205)
Set @ msgstatement = @ msgstatement + 'execute ,'
If exists (select * from [DBO]. [sysprotects] Where [ID] = @ objectid and [uid] = @ databaseroleid and [action] = 26 and [protecttype] = 205)
Set @ msgstatement = @ msgstatement + 'references ,'

If exists (select * from [DBO]. [sysprotects] Where [ID] = @ objectid and [uid] = @ databaseroleid and [action] = 198 and [protecttype] = 205)
Set @ msgstatement = @ msgstatement + 'create table ,'
If exists (select * from [DBO]. [sysprotects] Where [ID] = @ objectid and [uid] = @ databaseroleid and [action] = 203 and [protecttype] = 205)
Set @ msgstatement = @ msgstatement + 'create database ,'
If exists (select * from [DBO]. [sysprotects] Where [ID] = @ objectid and [uid] = @ databaseroleid and [action] = 207 and [protecttype] = 205)
Set @ msgstatement = @ msgstatement + 'create view ,'
If exists (select * from [DBO]. [sysprotects] Where [ID] = @ objectid and [uid] = @ databaseroleid and [action] = 222 and [protecttype] = 205)
Set @ msgstatement = @ msgstatement + 'create procedure ,'
If exists (select * from [DBO]. [sysprotects] Where [ID] = @ objectid and [uid] = @ databaseroleid and [action] = 224 and [protecttype] = 205)
Set @ msgstatement = @ msgstatement + 'execute ,'
If exists (select * from [DBO]. [sysprotects] Where [ID] = @ objectid and [uid] = @ databaseroleid and [action] = 228 and [protecttype] = 205)
Set @ msgstatement = @ msgstatement + 'backup database ,'
If exists (select * from [DBO]. [sysprotects] Where [ID] = @ objectid and [uid] = @ databaseroleid and [action] = 233 and [protecttype] = 205)
Set @ msgstatement = @ msgstatement + 'create default ,'
If exists (select * from [DBO]. [sysprotects] Where [ID] = @ objectid and [uid] = @ databaseroleid and [action] = 235 and [protecttype] = 205)
Set @ msgstatement = @ msgstatement + 'backup log ,'
If exists (select * from [DBO]. [sysprotects] Where [ID] = @ objectid and [uid] = @ databaseroleid and [action] = 236 and [protecttype] = 205)
Set @ msgstatement = @ msgstatement + 'create rule ,'
If exists (select * from [DBO]. [sysprotects] Where [ID] = @ objectid and [uid] = @ databaseroleid and [action] = 26 and [protecttype] = 205)
Set @ msgstatement = @ msgstatement + 'references ,'
If exists (select * from [DBO]. [sysprotects] Where [ID] = @ objectid and [uid] = @ databaseroleid and [action] = 178 and [protecttype] = 205)
Set @ msgstatement = @ msgstatement + 'create function ,'

If Len (@ msgstatement)> 0
Begin
If right (@ msgstatement, 1) = ','
Set @ msgstatement = left (@ msgstatement, Len (@ msgstatement)-1)
Set @ msgstatement = 'Grant '+ char (13) +
Char (9) + @ msgstatement + char (13)
If @ objectid <> 0
Set @ msgstatement = @ msgstatement + char (9) + 'on' + @ objectname + char (13)
Set @ msgstatement = @ msgstatement + char (9) + 'to' + @ databaserolename
Print @ msgstatement
End
Set @ msgstatement =''
If exists (select * from [DBO]. [sysprotects] Where [ID] = @ objectid and [uid] = @ databaseroleid and [action] = 193 and [protecttype] = 206)
Set @ msgstatement = @ msgstatement + 'select ,'
If exists (select * from [DBO]. [sysprotects] Where [ID] = @ objectid and [uid] = @ databaseroleid and [action] = 195 and [protecttype] = 206)
Set @ msgstatement = @ msgstatement + 'insert ,'
If exists (select * from [DBO]. [sysprotects] Where [ID] = @ objectid and [uid] = @ databaseroleid and [action] = 197 and [protecttype] = 206)
Set @ msgstatement = @ msgstatement + 'Update ,'
If exists (select * from [DBO]. [sysprotects] Where [ID] = @ objectid and [uid] = @ databaseroleid and [action] = 196 and [protecttype] = 206)
Set @ msgstatement = @ msgstatement + 'delete ,'
If exists (select * from [DBO]. [sysprotects] Where [ID] = @ objectid and [uid] = @ databaseroleid and [action] = 224 and [protecttype] = 206)
Set @ msgstatement = @ msgstatement + 'execute ,'
If exists (select * from [DBO]. [sysprotects] Where [ID] = @ objectid and [uid] = @ databaseroleid and [action] = 26 and [protecttype] = 206)
Set @ msgstatement = @ msgstatement + 'references ,'
If Len (@ msgstatement)> 0
Begin
If right (@ msgstatement, 1) = ','
Set @ msgstatement = left (@ msgstatement, Len (@ msgstatement)-1)
Set @ msgstatement = 'deny' + char (13) +
Char (9) + @ msgstatement + char (13) +
Char (9) + 'on' + @ objectname + char (13) +
Char (9) + 'to' + @ databaserolename
Print @ msgstatement
End
Fetch
Next
From _ sysobjects
Into
@ Objectid,
@ Objectname
End
Close _ sysobjects
Deallocate _ sysobjects
Drop table #
Print 'Go'
End