Solved

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

Posted on 2011-02-28
8
1,060 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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 500 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query to Add Late Tolerance 10 82
SQL Server - Set Field Values ito Zero Based on Related Table 4 44
Change this SQL to get all nodes 3 37
SQL- GROUP BY 4 22
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

740 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