Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-02-28
8
Medium Priority
?
1,074 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

721 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