Share via

Default database role members are not scripted when we use the "Generate Script Wizard"

The default database role members are not scripted when we use the "Generate Script Wizard"

Cause

======

This is an issue with the Generate Script Wizard code. It considers the rolemember as a part of Permissions for the Role and hence scripts the only user which is a member of the user Role.

You can use the following workaround to script out the default database role members

Workaround

==========

--Make sure you turn to Text mode (Ctrl+T)

--Excute the below script, Copy paste the results in a query window and execute

-----Begining of Script-------

Set Nocount On

Set Quoted_Identifier Off

--Creating the command to use Database

Declare @Usedb Varchar(128), @Dbname Varchar(128)

Set @Dbname = (Select Db_Name())

Set @Usedb = 'Use ['+@Dbname+'];'

Select @Usedb

--Create temporary Table with roles and role membership and if role isFixedRole

Create Table #tempTbl (id Int Identity(1,1), roleName nVARCHAR(1028), memberName

nVARCHAR(1028), isFixedRole Int);

Insert Into #tempTbl Select DbRole = g.name, MemberName = u.name, FixedRole =

g.is_fixed_role

From sys.database_principals u, sys.database_principals g,

sys.database_role_members m

Where g.principal_id = m.role_principal_id

and u.principal_id = m.member_principal_id and u.name != 'dbo'

Order By 1, 2

--Create temporary Table with Distinct user define database roles

Create Table #tempTblRole (id Int identity(1,1), roleName nVARCHAR(1028));

Insert Into #tempTblRole Select Distinct roleName From #tempTbl Where isFixedRole = 0;

--Declare variables

Declare @maxID Int;

Declare @counter numeric

Declare @roleName nVARCHAR(1028)

Declare @memberName nVARCHAR(1028)

Declare @cmd nVARCHAR(2048)

--Loop to Create sp_addrole statements

Select @counter = 0

Set @maxID = (Select max(id) From #tempTblRole)

While (@counter < @maxID)

Begin

Select @counter = @counter + 1

Select @roleName = (Select roleName From #tempTblRole Where id = @counter)

--EXEC sp_addrole 'Managers'

Select @cmd = 'EXEC sp_addrole "' + @roleName + '"'

Print @cmd

End

--Loop to Create sp_addrolemember statements

Select @counter = 0

Select @cmd = ''

Set @maxID = (Select max(id) From #tempTbl)

While (@counter < @maxID)

Begin

Select @counter = @counter + 1

Select @roleName = (Select roleName From #tempTbl Where id = @counter)

Select @memberName = (Select memberName From #tempTbl Where id = @counter)

Select @cmd = 'EXEC sp_addrolemember "' + @roleName + '", "' + @memberName +

'";'

Print @cmd

End

--Drop temporary Tables

Drop Table #tempTbl;

Drop Table #tempTblRole;

Set Nocount Off;

Set Quoted_Identifier On;

------End of Script-------

/*Sample Output

EXEC sp_addrolemember "db_accessadmin", "User1";

EXEC sp_addrolemember "db_backupoperator", "User1";

EXEC sp_addrolemember "db_datareader", "User1";

*/

Levi Justus

Technical Lead, Microsoft Sql Server

Comments