Script Roles User Belongs to in SQL 2005

[avalenti]

I cannot figure out how to script the roles that users belong to in SQL 2005 Management Studio. I can get it to script the users and I can get it to script the Roles in the database. But I cannot get it to script the users as members of the roles that they belong to in SQL 2005.
In SQL 2000 Enterprise Manager, you could right click on a database and go to All Tasks>Generate SQL Script and go to the Options tab and select 'Script database users and database roles' and it would give you the sp_grantdbaccess and sp_addrolemember statements to recreate the users correctly.
Let me know if you have figured out how to do the same thing in 2005 yet.

Thanks,
Anthony

[rmiao]

Just run sp_helpuser.

[avalenti]

Well that's fine if I just want to see what role each user belongs to and type them in manually. But I want to script the users out of each database so that I can add the users back after a restore with the same roles as before. Usually the restore is for a test environment and the users become orphaned. There are many databases and doing all of it manually doesn't seem like a good option. Why wouldn't MS include at least the same scripting functionality as we had before with Enterprise Manager?

[rmiao]

You can send suggestion to this link: http://connect.microsoft.com/Main/co...ContentID=2220