contained database user demo.sql

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


*/