InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 05:25:22 GMT20http://www.sqlservercentral.com/Forums/Topic440442-1178-1.aspxhelped me a lot, thanx.Sun, 20 Jun 2010 11:42:18 GMTHenrik Schützehttp://www.sqlservercentral.com/Forums/Topic440442-1178-1.aspxReally helpful and saved me a heap of time - thanks, much appreciated.Thu, 25 Sep 2008 04:26:11 GMTrussella0-96423http://www.sqlservercentral.com/Forums/Topic440442-1178-1.aspxWow...I [b]wish [/b]I'd found this [b]before [/b]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.[u]Gotcha #1[/u]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.[u]Cosmetic Tweak #1[/u]I added another section to CREATE LOGINs; since I don't need it that often I wrap it inside /* */[u]Cosmetic Tweak #2[/u]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 wayI 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.Thu, 29 May 2008 14:49:11 GMTmstjeanhttp://www.sqlservercentral.com/Forums/Topic440442-1178-1.aspxI 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 [i]syntax error near ' '.[/i] 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.Wed, 20 Feb 2008 07:49:48 GMTEdVassiehttp://www.sqlservercentral.com/Forums/Topic440442-1178-1.aspxWhat 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 --Wed, 20 Feb 2008 07:26:08 GMTMark D Powellhttp://www.sqlservercentral.com/Forums/Topic440442-1178-1.aspxsp_ScriptRoles is one of many useful scripts in SQL Server Finebuild. Go to http://www.codeplex.com/SQLServerFineBuild to get the whole package.Wed, 20 Feb 2008 02:46:30 GMTEdVassiehttp://www.sqlservercentral.com/Forums/Topic440442-1178-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/SQL+2000/61878/">Script a Database Role</A>[/B]Wed, 09 Jan 2008 00:27:48 GMTEdVassie