Compile a script to generate custom roles and permissions

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.