CODEWITHSUNDEEP
permissionsschemasql-server-2008-r2
Dinesh
Dinesh

Reputation: 2066

Schema Permissions in SQL Server 2008 R2

I have 2 Schema and 3 generic users to access the data from my DB

Schema : EMSMaster and EMSTransactional
Users : BN_USER, MBN_USER and SUPER_ADMIN

My permissions should be like

BN_USER  - select on EMSMaster 
       select, insert and update on EMSTransactional

MBN_USER - select, insert, update on EMSMaster
       select, insert, update and Delete on EMSTransactional

Is there any way to give this kind of permissions on Schema? Please suggest me some good way to implement this kind of permissions.

Upvotes: 1

Views: 4458

Answers (1)

juergen d
juergen d

Reputation: 204746

GRANT SELECT ON schema :: EMSMaster TO BN_USER 
GRANT SELECT, INSERT, UPDATE ON schema :: EMSTransactional TO BN_USER 

GRANT SELECT, INSERT, UPDATE ON schema :: EMSMaster TO MBN_USER 
GRANT SELECT, INSERT, UPDATE, DELETE ON schema :: EMSTransactional TO MBN_USER

Upvotes: 2

Related Questions