Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-02-28
8
Medium Priority
?
1,080 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
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.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Screencast - Getting to Know the Pipeline

886 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