Sql-server – How to write an SQL script to copy the securables, memberships and owned schemas of a current database user

sql serversql-server-2016

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

Best Answer

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.

Related Solutions

Sql-server – How to create database, and add user linked to login

Not sure how dbo is coming into play. Here is how you create a user in a database mapped to a server login, whether that login is already associated to a user in any other database:

CREATE LOGIN foo WITH PASSWORD = 'bar', CHECK_POLICY = OFF;
GO
CREATE DATABASE splunge;
GO
USE splunge;
GO
CREATE USER foo FROM LOGIN [foo];
GO
-- then permissions:
GRANT ... TO foo;
EXEC sp_addrolemember N'<role>', N'foo';
etc.

Sql-server – Changing Schema ownership and side effects

In SQL Server, changing the schema owner will drop all permissions. This is a "feature" buried deep in the documentation:

If the target entity is not a database and the entity is being transferred to a new owner, all permissions on the target will be dropped.

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-authorization-transact-sql

For a schema, this apparently applies to the permissions on all objects within it.

Best Answer

Related Solutions

Sql-server – How to create database, and add user linked to login

Sql-server – Changing Schema ownership and side effects

Related Question