?
Solved

SQL Server 2005: Create the same User Role on New Database

Posted on 2011-02-28
8
Medium Priority
?
1,084 Views
Last Modified: 2012-05-11
Is it possible to export a user role from one MSSQL database and create the same role with same permissions on a new database?
0
Comment
Question by:street9009
  • 3
  • 3
  • 2
8 Comments
 
LVL 17

Expert Comment

by:pssandhu
ID: 35003398
0
 

Author Comment

by:street9009
ID: 35003448
Hmm. I kinda understand, but I'm not sure I know how to use it.
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 35003486
Here is another article from Microsoft explaining the same. I do not think there is a quick way to do this.

http://support.microsoft.com/kb/246133

P.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:pssandhu
ID: 35003488
Another one with screen shots. May be this is a little simpler to follow:

http://www.techrepublic.com/blog/howdoi/how-do-i-transfer-logins-from-one-sql-server-2005-instance-to-another/140

P.
0
 
LVL 6

Expert Comment

by:anushahanna
ID: 35003593
the above are good for logins, which is step#1 and needful before getting the users and user roles in (db_datareader etc)

http://www.sql-server-performance.com/articles/dba/object_permission_scripts_p1.aspx
gives you script to do the database roles.
0
 

Author Comment

by:street9009
ID: 35003618
I just need a script that will output what I need to create a custom user role on any database. I already have the proper queries to grant the users that role and revoke the others.
0
 
LVL 6

Accepted Solution

by:
anushahanna earned 2000 total points
ID: 35003907
>>I just need a script that will output what I need to create a custom user role on any database.

do you mean UserA is in DatabaseA- you want to script that user and apply it on DatabaseB and DatabaseC. You can do that with the link I gave you, but you will face errors, as the logins and users SID should be the same.

They are in
select * from master..syslogins
and
select * from DB_Local.dbo.sysusers
you can also run EXEC SP_HELPUSER

you will need the system proc sp_change_users_login to align the SIDs
http://msdn.microsoft.com/en-us/library/ms174378.aspx

It is always going to multiple steps to confirm and make sure the security will work.. once you get used to it, it will go easy and fast.

In the above scenario, it is best to create the login or make sure the login is there.

then you can use the
CREATE USER ABC for LOGIN XYZ format.
http://msdn.microsoft.com/en-us/library/ms173463.aspx
This will automatically generate the right SID and align the user and login.

After that you can assign proper roles like

exec sp_addrolemember db_datareader ABC
exec sp_addrolemember db_datawriter ABC

The other database roles are
http://msdn.microsoft.com/en-us/library/ms189121%28v=sql.90%29.aspx

Hopefully this gives a more complete picture.

scripting from another server/database, paste into another database will not work without some careful observation and tweaking.
0
 

Author Closing Comment

by:street9009
ID: 35003921
Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question