Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
USE master;
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'contained database authentication', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
IF (DB_ID('OnlyMe') IS NOT NULL)
BEGIN
ALTER DATABASE onlyme SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE onlyme;
END;
GO
CREATE DATABASE [onlyme] CONTAINMENT = PARTIAL
ON PRIMARY
(
NAME = N'OnlyMe',
FILENAME = N'C:\MSSQL\DATA\OnlyMe.mdf',
SIZE = 8192KB,
FILEGROWTH = 65536KB
)
LOG ON
(
NAME = N'OnlyMe_log',
FILENAME = N'C:\MSSQL\DATA\OnlyMe_log.ldf',
SIZE = 8192KB,
FILEGROWTH = 65536KB
);
GO
USE [onlyme]
GO
CREATE USER [me] WITH PASSWORD=N'Password23!'
GO
USE [onlyme]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [me]
GO
USE [onlyme]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [me]
GO
ALTER ROLE [db_owner] ADD MEMBER [me]
GO
/*
to connect to the database, open a new session
this user only has perms in the contained database
you need to use "options" in the connection diaglog, go to the connection properties tab and specify the database name onlyme
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment