怎么样才能导出非固定角色执行语句权限的脚本(再发贴求解答)

Question

Proc to script a user role and object permissionshttp://www.sqlservercentral.com/scripts/contributions/520.asp

Answer 1

Use master
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ScriptRole]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_ScriptRole]
GOSET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
Create Procedure sp_ScriptRole
(
 @RoleName varchar(85)
)
AS/********1*********2*********3*********4*********5*********6*********8*********9*********0*********1*********2*****
**
**  $Archive$
**  $Revision$
**  $Author$ 
**  $Modtime$
**
*******************************************************************************************************************
**
**  $Log$
**
*******************************************************************************************************************
**
** Name: sp_ScriptRole
** Desc: This procedure prepares a script of the role and permissions 
** of a given role (or user) in the current database.
** NOTE: This only scripts the permissions in the current database for a given role or user on user objects
** It does not script extended permissions such as create table, create view, backup database or 
** system roles
**
** Return values: 0 = Successful, error number if failed
**              
*******************************************************************************************************************
** Change History - All Author comments below this point.
*******************************************************************************************************************
**  Author Date Description
**  ------- -------- -------------------------------------------
**  NBJ 10-Oct-2002 Original - SP to script a user role
******************************************************************************************************************/Declare    @Err  int
Set nocount on
Select @Err = 0If @RoleName is Null
Begin
Select @RoleName = 'my_default_role'
End
-- This table is to store the type of user action 
-- that is coded in the sysprotects system table  e.g Insert, Update, Select
CREATE TABLE #tblaction (
[Action] [int] NOT NULL ,
[Name] [varchar] (85) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
)
-- This table is to store the Grant or Revoke info that is coded in the sysprotects system table
CREATE TABLE #tblprotecttype (
[protecttype] [int] NULL ,
[Name] [varchar] (85) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
)
-- These values can be found in SQL Books Online in the definition of the sysprotects table
Insert #tblprotecttype (protecttype, Name) Values (204,'GRANT_W_GRANT')
Insert #tblprotecttype (protecttype, Name) Values (205,'GRANT')
Insert #tblprotecttype (protecttype, Name) Values (206,'REVOKE')
Insert #tblaction (Action, Name) Values (26,'REFERENCES')
Insert #tblaction (Action, Name) Values (178,'CREATE FUNCTION')
Insert #tblaction (Action, Name) Values (193,'SELECT')
Insert #tblaction (Action, Name) Values (195,'INSERT')
Insert #tblaction (Action, Name) Values (196,'DELETE')
Insert #tblaction (Action, Name) Values (197,'UPDATE')
Insert #tblaction (Action, Name) Values (198,'CREATE TABLE')
Insert #tblaction (Action, Name) Values (203,'CREATE DATABASE')
Insert #tblaction (Action, Name) Values (207,'CREATE VIEW')
Insert #tblaction (Action, Name) Values (222,'CREATE PROCEDURE')
Insert #tblaction (Action, Name) Values (224,'EXECUTE')
Insert #tblaction (Action, Name) Values (228,'BACKUP DATABASE')
Insert #tblaction (Action, Name) Values (233,'CREATE DEFAULT')
Insert #tblaction (Action, Name) Values (235,'BACKUP LOG')
Insert #tblaction (Action, Name) Values (236,'CREATE RULE')-- Get role, objectname and object id into a table for given role
select @RoleName as Role, name AS objectname, id, xtype into #tmpX1 from sysobjects where id in 
(select id from sysprotects where uid in 
(select uid from sysusers where name = @RoleName)) 
Select @Err = @@Error
If @Err <> 0
Begin
Return @Err
End
-- Get the protection information for each object from sysprotects
select P.id as [ID], tP.Name as [Action], tA.Name as [ProtectType] into #tmpX2 from  #tblprotecttype tP, sysprotects P, #tblaction tA 
where P.action = tA.action AND P.protecttype = tP.protecttype AND  P.uid in 
(select uid from sysusers where name = @RoleName)  
Select @Err = @@Error
If @Err <> 0
Begin
Return @Err
End
-- Script the role itself,  if needed this can be modified to test for existence first.
Select 'Exec sp_addrole ' + @RoleName + char(10) + 'go'
-- Generate the Grant and Revoke statements for each object.
Select rtrim(Action) + ' ' + rtrim(ProtectType) + ' ON ' + rtrim(objectname) + ' TO ' + rtrim(role) + char(10) + 'go' from #tmpX1 Inner Join #tmpX2 ON #tmpX1.id = #tmpX2.id order by objectname
Select @Err = @@Error
If @Err <> 0
Begin
Return @Err
End
-- Housekeeping
drop table #tmpX1, #tmpX2, #tblprotecttype, #tblaction
Select @Err = @@Error
If @Err <> 0
Begin
Return @Err
End
Return 0GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Answer 2

不好意思，看错了，正确的应该这样：企业管理器-所有任务-生成SQL脚本，常规选项卡中单击"全部显示",选中"编写全部对象脚本"，在"设置格式"选项卡中，取消选中"为每个对象生成Create命令"，“为每个对象生成drop命令”.在"选项"选项卡中，选中"编写数据库用户和数据裤脚色脚本"，"编写对象级别权限的脚本",确定using the‘Generate Scripts’ option in Enterprise Manager. If you choose the
option to ‘script all objects’ on the General Tab, then remove the
‘Create’ and ‘Drop’ options from the Formatting Tab and then select
‘Script object level permissions’ from the Options Tab, you should just
end up with just the permissions scripts

Answer 3

to: vivianfdlpw
谢谢！！
但通过你的方法生成的sql脚本仍然没有
授予角色createrole执行
create table,create proc,create view,create function,create rule,create default
这些语句的权限脚本。
请你测试一下！！！

Answer 4

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 @msgStatementSELECT
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] = @DatabaseRoleIDINSERT # SELECT 0,@DatabaseRoleNameDECLARE _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

Answer 5

--结果
/*
GO
--Security creation script for role createrole
--Created At: 20050926144431
--Created By: Ldyia 
--Add Role To Database
EXEC sp_addrole
@rolename 'createrole'
--Set Object Specific Permissions For Role
GRANT
CREATE TABLE,CREATE VIEW,CREATE PROCEDURE,CREATE DEFAULT,CREATE RULE,CREATE FUNCTION
TO createrole
GO
*/

Answer 6

to vivianfdlpw
这样是可以,但是如果我要导库（即完全复制一个库到另一台server上（当然包括用户和角色以及权限控制）），应该怎样做？？
就是说怎么样才能复制一模一样的库？？

Answer 7

使用邹健的http://blog.csdn.net/zjcxc/archive/2005/08/20/459401.aspx
是不可以了吗？？？

Answer 8

连企业管理器中的生成sql脚本都无法导出这些语句，是否可以说这是生成sql脚本的一个BUG??

Answer 9

也可以先备份还原数据库，然后晴空数据： exec sp_msforeachtable 'truncate table ?'
然后把权限脚本复制一下执行