Moving user roles to another server/database

Question

Moving user roles to another server/database

Yuri55

SSCrazy Eights

Points: 8441
More actions
June 16, 2005 at 2:06 pm

#93210

Need some help/tip how to move/copy roles I’ve created on developer DB1 (server SVR1) to production DB2 (server SVR2)? I found couple scripts but they are working on the same server. Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Hans Lindgren SSChampion Points: 10494 More actions · Answer 1

I guess you could write your own?

Insert the result of sp_helprole into a tmptable and just do sp_addrole for every record (execpt the standard roles) on the new server...

//Hanslindgren

Yuri55 SSCrazy Eights Points: 8441 More actions · Answer 2

Thanks for reply. Actually I was going to do the same way, i.e. insert data (role permissions and object) into table, import this table to production server and start another script to create role with permissions from table.

Thanks

Hans Lindgren SSChampion Points: 10494 More actions · Answer 3

Why not make boring things easier?

Create a linked server and.. Tadaa! Do a EXEC YourServer.Master.dbo.sp_addrole 'MyRole'

You're welcome

Yuri55 SSCrazy Eights Points: 8441 More actions · Answer 4

Thanks for reply. Probably, it's good idea, I mean regarding linked server. But is there a reason to create a linked server for one query, I mean, are there any known drawbacks (my concern is primarily about production server)? Unfortunately, I don't have a lot of experience in this area. Thanks

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 5

I wouldn't add a linked server to production due to the security concerns that could raise.

Otherwise, the cursor option above or maybe an app that uses SQLDMO. DBArtisan will allow you to script a role and the permissions associated. Other than that, elbow grease is the only option here that I'm aware of.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Hans Lindgren SSChampion Points: 10494 More actions · Answer 6

I guess it is a point of view. Not many things are security issues unless you explicitly make them to be security issues. Defining a Linked Server where all users are using 'sa' on the other side is one of those. But that is effectively the same as saying that SQL Server shouldn't be installed because of security issues (if everyone is handed the 'sa' password...)

//Hanslindgren

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 7

True. And, assuming you're only going to run the scripts and then drop the linked server, it's not that big a deal. However, it is a bad practice from a security standpoint to connect dev to production through an 'sa' level of account.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Hans Lindgren SSChampion Points: 10494 More actions · Answer 8

Hmm. Just by connecting them with 'sa' capabilities I am not sure has to be considered to be bad practice. Althrough I would connect a specific user (I.e A user would have it's credentials forwarded to the other server and let it decide what credentials your user should have or just let an administrative account have rights to the Linked Server) through the linked server and not touch the 'sa' account. Second thing is that the user only needs 'Read' permission in the Dev if it is linked from production-to-dev...