Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script a Database Role Expand / Collapse
Author
Message
Posted Wednesday, January 9, 2008 12:27 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:14 AM
Points: 2,868, Visits: 3,215
Comments posted to this topic are about the item Script a Database Role

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,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
Posted Wednesday, February 20, 2008 2:46 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:14 AM
Points: 2,868, Visits: 3,215
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: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,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
Posted Wednesday, February 20, 2008 7:26 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 11:08 AM
Points: 1,371, Visits: 385
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
Posted Wednesday, February 20, 2008 7:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:14 AM
Points: 2,868, Visits: 3,215
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: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,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
Posted Thursday, May 29, 2008 2:49 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 1:26 PM
Points: 436, Visits: 2,278
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
Posted Thursday, September 25, 2008 4:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 14, 2014 7:48 AM
Points: 70, Visits: 223
Really helpful and saved me a heap of time - thanks, much appreciated.


Post #575828
Posted Sunday, June 20, 2010 11:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 8, 2014 2:44 PM
Points: 18, Visits: 104
helped me a lot, thanx.

Post #940057
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse