Script a Database Role

Question

Post reply

Script a Database Role

EdVassie

SSC Guru

Points: 60472
More actions
January 9, 2008 at 12:27 am

#182654

Comments posted to this topic are about the item Script a Database Role
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

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

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

EdVassie SSC Guru Points: 60472 More actions · Answer 1

sp_ScriptRoles is one of many useful scripts in SQL Server Finebuild.

Go to http://www.codeplex.com/SQLServerFineBuild to get the whole package.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Mark D Powell SSCarpal Tunnel Points: 4775 More actions · Answer 2

What version of SQL Server is this for?

Using SQL Server 2000 SP4 if I follow the execute directions I get procedure not found errors. If I change the database name in the exec example to master it executes cleanly but the results do not appear correct.

I have some SQL Server 2005 system but I will not be able to test on one of them till later.

-- Mark D Powell --

EdVassie SSC Guru Points: 60472 More actions · Answer 3

I have used it with SQL 2000 SP3 and SP4, and also on SQL 2005.

I have just tried to create the script on my machine, and got some syntax errors. It looks like some tab characters in the original script have got corrupted. If you get any errors like syntax error near ' '. then click on the error message and er-type all space characters on the offending line.

I will try to get a nwe version of the script published without this problem.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

mstjean Hall of Fame Points: 3310 More actions · Answer 4

Wow...I wish I'd found this before I rolled my own!

I'm catching up on some old sqlcentral emails... and found this.

I had to move a db from our production server to a testing second server-- or to the same server with different name, used for a different purpose (from production to a developer's copy for example). I was altering security settings [gakk!]manually[/gakk!] and finally wrote my routine. I run the script against the "target" db, restore src over target, drop ALL perms in the newly restored target db, run the output from step1 script on the newly restored target db so the new target has the same perms it did before getting overwritten.

To support the above process you may also want a script to drop all permissions from a db. Of course this is WAAAAAYYY dangerous; something to keep next to your test tubes of typhoid, under lock and key.

Gotcha #1

If you use this as outlined above and you have ANY sql-authenticated users in the db be sure you DROP those users after the restore and then re-add them. Early in this process I'd see SQLUSER in the restored db, confirm it had correct perms in the db and figure I was done. But if you look at the server LOGIN for SQLUSER you'd see that it had no perms in the restored db. The db user and the server login are not the same account (sid) but if you try to add perm to the db to the server login it barks and tells you SQLUSER already exists...until you DROP USER in the db.

So you have to drop the sql-authenticated users from the db after restoring it-- and then add them again. If this is the first time you also have to do some CREATE LOGINs for those logins not on the target server. When I CREATE LOGIN for a sql authenticated account I put a dummy password in the script and either run with the dummy pw or manually alter to the proper pw when I run the generated script.

Cosmetic Tweak #1

I added another section to CREATE LOGINs; since I don't need it that often I wrap it inside /* */

Cosmetic Tweak #2

In mine, I return it all to a single resultset rather than multiples. It makes it a matter of selecting everything from one panel instead of multiple:

* Create a table variable CREATE @ret TABLE (rowid int identity,code varchar(500)

* Each of your "SELECT..." turns into "INSERT INTO @ret SELECT..."

* And the last step is "SELECT code FROM @ret"

* To turn the column headings from each of your selects into a row, just "INSERT INTO @ret SELECT '-- Create Roles"

* Add whatever comments you want as hints or white space the same way

I didn't want to put an sp into master so whenever I need to get a db permissions scripted out I just drop the code into QA and exec it in the right db.

Cursors are useful if you don't know SQL