How do I write an SQL script to copy the securables, memberships and owned schemas of a current database user?

Question

I currently have to repeatedly restore databases on my local machine for development work. I then have to manually add database users to the master level login section so that I can allow my web app to authenticate with the database. I can see that you can right-click on the user -> Script User as -> Create To -> New Query editor window. The resulting SQL does not artificially mimic any of the securables, memberships, or owned schemas.

I already found that you can programmatically add a user to the database that uses the Master login information.

CREATE USER [user_name] FOR LOGIN [user_name] WITH DEFAULT_SCHEMA=[dbo] GO

When you restore database all user permission (at database level) are copied with it. You only have to create login and match SID and it should work. Why do you need to script out user level information? — SqlWorldWide, May 19 '17 at 18:45
First result. http://stackoverflow.com/questions/1987190/scripting-sql-server-permissions — Jonathan Fite, May 19 '17 at 19:06
@SqlWorldWide, that ended up helping me connect the dots. I thought You had to start at the login level and have your user created on the database from the master level. — Jolley71717, May 19 '17 at 19:52
There are few scripts here you can use to match all sid after a restore. — SqlWorldWide, May 19 '17 at 19:54
@JonathanFite, that link was directed at stored procedures and not users, but still interesting information — Jolley71717, May 19 '17 at 19:55
@Jolley71717 Did you only read the first line of the question? on stored procedures **and other stuff** and if you look at the accepted answer it does not do any sort of limits on only stored procedures. — Aaron Bertrand, May 19 '17 at 20:26
@AaronBertrand, thank you for pointing that out. I did skim that answer. I went back through and actually ran the code. It does exactly what I was asking for. Thanks for being patient with me — Jolley71717, May 19 '17 at 20:31

score 1 · Accepted Answer · answered May 19 '17 at 22:18

When you restore a database all the users and privilege assigned to users (including database role membership) from that database get restored at the destination server. If you already have a login in the destination server with matching Server-Level Identification Number (SID) then you do not have to do anything.

If you do not have a login you will need to create one:

Copy login from source to destination using this or this.

You have have a login with different SID you can match those using solution from here.

How do I write an SQL script to copy the securables, memberships and owned schemas of a current database user?

1 Answers1