Script a Database Role

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ed Vassie » Script a Database Role

Script a Database Role

Rate Topic

Display Mode

Topic Options

Author

Message

EdVassie

Posted Wednesday, January 09, 2008 12:27 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:26 AM
Points: 2,621, Visits: 2,759

Comments posted to this topic are about the item Script a Database Role

Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2012, 2008 R2, 2008 and 2005. 25 March 2013: now over 23,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.

Post #440442

EdVassie

Posted Wednesday, February 20, 2008 2:46 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:26 AM
Points: 2,621, Visits: 2,759

sp_ScriptRoles is one of many useful scripts in SQL Server Finebuild.
Go to http://www.codeplex.com/SQLServerFineBuild to get the whole package.

Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2012, 2008 R2, 2008 and 2005. 25 March 2013: now over 23,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.

Post #457885

Mark D Powell

Posted Wednesday, February 20, 2008 7:26 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 12:26 PM
Points: 1,258, Visits: 341

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

Post #457975

EdVassie

Posted Wednesday, February 20, 2008 7:49 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:26 AM
Points: 2,621, Visits: 2,759

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.

Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2012, 2008 R2, 2008 and 2005. 25 March 2013: now over 23,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.

Post #457992

mstjean

Posted Thursday, May 29, 2008 2:49 PM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:15 PM
Points: 423, Visits: 1,948

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

Post #508711