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