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

  • Article

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