SQL User for a Specific Database (Partial Containment)

Run the following MS SQL Server query to create a SQL user with access to a specific SQL database that has partial containment. This user won’t be able to view or access other DBs on the same SQL instance.

CODE

Use [database_name]
GO

CREATE USER [user_name] WITH PASSWORD = 'password'
GO

To limit the SQL schemas for this user, run the following query:

CODE

GRANT SELECT ON SCHEMA::[schema_name] TO [user_name]
GO

To convert a SQL database to work with partial containment, run the following query:

CODE

ALTER DATABASE [database_name] SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO

If you are unable to convert the database to partial containment, run the following query first:

CODE

EXEC sp_configure 'show advanced', 1
GO

RECONFIGURE
GO

EXEC sp_configure 'contained database authentication', 1
GO

RECONFIGURE
GO