USE [master]
GO
CREATE LOGIN LGX_USR WITH PASSWORD=N’‘
GO
CREATE LOGIN AUD_USR WITH PASSWORD=N’‘
GO
CREATE LOGIN DM_USR WITH PASSWORD=N’‘
GO

USE LGX_SOLUTION
GO
CREATE USER LGX_USR FOR LOGIN LGX_USR
GO
ALTER ROLE [db_owner] ADD MEMBER LGX_USR
GO

CREATE SCHEMA lgx
go

DENY select,update,alter, control, delete,insert,execute,references ON schema::[dbo] TO LGX_USR
GRANT select,update,alter,delete,control,insert,execute,references ON schema::lgx TO LGX_USR
GRANT create function, create procedure TO LGX_USR
grant create table to LGX_USR;
grant create table to LGX_USR;

alter user LGX_USR with DEFAULT_SCHEMA = lgx

CREATE ROLE lgx_role AUTHORIZATION LGX_USR
Go

GRANT ALTER, EXECUTE, SELECT, INSERT, UPDATE, DELETE, CONTROL, REFERENCES, VIEW DEFINITION
ON SCHEMA :: lgx
TO lgx_role WITH GRANT OPTION
Go

GRANT TAKE OWNERSHIP
ON SCHEMA :: lgx
TO lgx_role WITH GRANT OPTION
Go

EXEC sp_addrolemember N’lgx_role’, LGX_USR

USE LGX_DATA
GO

create SCHEMA datamart
go

create SCHEMA lgxaudit
go

—-
—- datamart user
—-

CREATE USER DM_USR FOR LOGIN DM_USR
GO

deny select,update,alter,delete,control,insert,execute,references ON schema::lgxaudit TO DM_USR
GRANT select,update,alter,delete,control,insert,execute,references ON schema::datamart TO DM_USR
GRANT create function, create procedure TO DM_USR
grant create table to DM_USR;

alter user DM_USR with DEFAULT_SCHEMA = datamart

CREATE ROLE datamart_role AUTHORIZATION DM_USR
Go

GRANT ALTER, EXECUTE, SELECT, INSERT, UPDATE, DELETE, CONTROL, REFERENCES, VIEW DEFINITION
ON SCHEMA :: datamart
TO datamart_role WITH GRANT OPTION
Go

GRANT TAKE OWNERSHIP
ON SCHEMA :: datamart
TO datamart_role WITH GRANT OPTION
Go

EXEC sp_addrolemember N’datamart_role’, DM_USR

—-
—- AUD_USR
—-

CREATE USER AUD_USR FOR LOGIN AUD_USR
GO

DENY select,update,alter,control,delete,insert,execute,references ON schema::smartseg TO AUD_USR
DENY select,update,alter,control,delete,insert,execute,references ON schema::datamart TO AUD_USR
GRANT select,update,alter,delete,control,insert,execute,references ON schema::lgxaudit TO AUD_USR
grant create table to AUD_USR;
GRANT create function, create procedure TO AUD_USR
grant create table to AUD_USR;

alter user AUD_USR with DEFAULT_SCHEMA = lgxaudit

CREATE ROLE lgxaudit_role AUTHORIZATION AUD_USR
Go
Go

GRANT ALTER, EXECUTE, SELECT, INSERT, UPDATE, DELETE, CONTROL, REFERENCES, VIEW DEFINITION
ON SCHEMA :: lgxaudit
TO lgxaudit_role WITH GRANT OPTION
Go

GRANT TAKE OWNERSHIP
ON SCHEMA :: lgxaudit
TO lgxaudit_role WITH GRANT OPTION
Go

EXEC sp_addrolemember N’lgxaudit_role’, AUD_USR

Revision: 2
Last modified: May 01, 2019

Feedback

Was this helpful?

Yes No
You indicated this topic was not helpful to you ...
Could you please leave a comment telling us why? Thank you!
Thanks for your feedback.

Post your comment on this topic.

Post Comment