--Create a test database include: login, user, role, schema, and a simple table:
--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]
No comments:
Post a Comment