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.
Use [database_name]
GO
CREATE USER [user_name] WITH PASSWORD = 'password'
GO
To limit the SQL schemas for this user, run the following query:
GRANT SELECT ON SCHEMA::[schema_name] TO [user_name]
GO
To convert a SQL database to work with partial containment, run the following query:
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:
EXEC sp_configure 'show advanced', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'contained database authentication', 1
GO
RECONFIGURE
GO