Solved

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

Posted on 2011-02-28
8
1,065 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: 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 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

617 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