CODEWITHSUNDEEP
sqlsql-serversql-server-2008
apomene
apomene

Reputation: 14389

Best Practice to Grant Access to specific tables, SQL

I create a user in SQL which I want to have read / write access to only 4 of the tables of my Database:

CREATE LOGIN MyUser 
    WITH PASSWORD = 'xyqe123';
USE [EODB];
GO
CREATE USER MyUser FOR LOGIN  MyUser;
GO

Now by using (brute force kinda), simplified, cost un-effective method I define:

GRANT select,insert,update,delete ON [MyDB].[dbo].[t1] TO [MyUser] 
GO
GRANT select,insert,update,delete ON [MyDB].[dbo].[t2] TO [MyUser] 
GO
GRANT select,insert,update,delete ON [MyDB].[dbo].[t3] TO [MyUser] 
GO
GRANT select,insert,update,delete ON [MyDB].[dbo].[t4] TO [MyUser] 
GO
Deny select,insert,update,delete ON [MyDB].[dbo].[t5] TO [MyUser] 
GO
Deny select,insert,update,delete ON [MyDB].[dbo].[t6] TO [MyUser] 
GO
Deny select,insert,update,delete ON [MyDB].[dbo].[t7] TO [MyUser] 
GO
.......
.......///continues for 14 more tables

The above meet my needs but as mention is quite "stupid". What I tried is to set [MyUser] to [denydatareader] and [denydatawriter] schemas and after grant the permission for the 4 tables, but it didn't worked.

My Questions is how can I achieve my goal using as less statements as possiple?

Upvotes: 1

Views: 8587

Answers (2)

Gary Walker
Gary Walker

Reputation: 9134

First, I would recommend you consider using custom database roles. Create logical roles based on the "logical role" of the user. If your app is based on Active Directory priv. (i.e., integrated logins) -- It is really a AD domain question. Otherwise you simple add/drop roles for a database user.

You then grant roles on the underlying tables, views, etc.

If you the question is simply, how do I simply grant roles on 14 different tables, there is nothing wrong in about using dynamic sql in this context. Roll your own routine that grants the roles for 1 table (passing in the name) and then call it 14 times.

Upvotes: 2

user275683
user275683

Reputation:

Instead of adding users to groups like datareader or denydatareader just use first 4 statement. As long as you did not add user to any groups, user will be restricted to those 4 tables.

GRANT select,insert,update,delete ON [MyDB].[dbo].[t1] TO [MyUser] 
GO
GRANT select,insert,update,delete ON [MyDB].[dbo].[t2] TO [MyUser] 
GO
GRANT select,insert,update,delete ON [MyDB].[dbo].[t3] TO [MyUser] 
GO
GRANT select,insert,update,delete ON [MyDB].[dbo].[t4] TO [MyUser]

Upvotes: 2

Related Questions