编写生成自定义角色和权限的脚本

王朝other·作者佚名  2006-11-24
窄屏简体版  字體: |||超大  

/*

改编自国外站点上的一篇文章,原文地址记不大清了

--vivianfdlpw 2005.9 引用请保留此信息

*/

DECLARE @DatabaseRoleName [sysname]

SET @DatabaseRoleName = 'createrole' --角色名

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_SCHEMA',

'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_SCHEMA',

'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

[1] [2] 下一页

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航