CODEWITHSUNDEEP
sqlsql-serversql-server-2012
mHelpMe
mHelpMe

Reputation: 6668

specific permissions to a table based on user

I am using SQL Server 2012.

I have a table which contains two columns. One is called Directory, the other UserName.

To explain my problem I think a simple example is easier.

So this table has 3 users. In the UserName column, only the 3 users, usernames can be entered plus one other entry called Default.

Directory     UserName
C:\Blah       Bob
C:\BlaF       Brad
C:\BlaK       Dave
C:\BlaPP      Default
C:\Anoth      Default

What I would like to know is if it is possible to allow only two of the users to be able to insert, delete records with the username Default and the other user to be able to only select the records with Default.

Is this possible?

Update

After doing some reading is it not possible to do this using an Instead of trigger? So in my understanding this trigger will fire before an insert, update or delete query is executed. So I was thinking in the trigger if could check the host pc (users on my team will only be using their computer) to see who it is trying to insert, update or delete and if its a user who doesn't have permission to edit the default list then exit the trigger and don't update the table. Or am I missing something?

Upvotes: 0

Views: 52

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31775

Instead of giving users direct access to the table, give the first two users access to a stored procedure that inserts to the table, and only inserts "Default" for the username.

Give the third user access to a view (or stored procedure) that only selects records where username="Default".

Upvotes: 1

Related Questions