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