1

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

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

8
  • 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. stackoverflow.com/questions/1987190/… – Jonathan Fite May 19 '17 at 19:06
  • This script will help you – Kin Shah May 19 '17 at 19:08
  • @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
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.