2

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

Jolley71717
  • 123
  • 1
  • 5

1 Answers1

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.

SqlWorldWide
  • 13,687
  • 3
  • 30
  • 54