Schema Level Permissions

Reference:

https://technet.microsoft.com/en-us/library/dd283095%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396

--create a test database

CREATE DATABASE [SecurityTest]

GO

USE SecurityTest

GO

CREATE TABLE [dbo].[table1](

       [pkcol] [int] IDENTITY(1,1) NOT NULL,

       [col1] [int] NULL,

PRIMARY KEY CLUSTERED ([pkcol])

)

GO

--create test user login

CREATE LOGIN [User1] WITH PASSWORD=N'p@55w0rd'

GO

--create user in test database

CREATE USER [User1] FOR LOGIN [User1] WITH DEFAULT_SCHEMA=[Developer_Schema]

GO

--create role

CREATE ROLE [Developer_Role] AUTHORIZATION [dbo]

GO

--create schema

CREATE SCHEMA [Developer_Schema] AUTHORIZATION [User1]

GO

--apply permissions to schemas

GRANT ALTER ON SCHEMA::[Developer_Schema] TO [Developer_Role]

GO

GRANT CONTROL ON SCHEMA::[Developer_Schema] TO [Developer_Role]

GO

GRANT SELECT ON SCHEMA::[Developer_Schema] TO [Developer_Role]

GO

GRANT DELETE ON SCHEMA::[dbo] TO [Developer_Role]

GO

GRANT INSERT ON SCHEMA::[dbo] TO [Developer_Role]

GO

GRANT SELECT ON SCHEMA::[dbo] TO [Developer_Role]

GO

GRANT UPDATE ON SCHEMA::[dbo] TO [Developer_Role]

GO

GRANT REFERENCES ON SCHEMA::[dbo] TO [Developer_Role]

GO

--ensure role membership is correct

EXEC sp_addrolemember N'Developer_Role ', N'User1'

GO

--allow users to create tables in Developer_Schema

GRANT CREATE TABLE TO [Developer_Role]

GO

--Allow user to connect to database

GRANT CONNECT TO [User1]