CODEWITHSUNDEEP
sql-serversql-server-2008permissions
Nu-hin
Nu-hin

Reputation: 690

MS SQLServer: How to deny table modification to everyone but a single user?

I have a table and I want to deny its modification to all users except one. One obvious solution is to create a special denying role and add all other roles to it. But in that case new roles will be still able to modify the table, unless added to this role. What is the decent way do set such permissions?

Upvotes: 0

Views: 389

Answers (1)

gbn
gbn

Reputation: 432180

You should GRANT rights (INSERT,UPDATE,DELETE) to the one special "write" role only. And no other rights except SELECT

There is no need to DENY because users do not have rights by default: DENY would be needed only if you GRANT rights in the first place.

Keep it simple.

Notes:

  • If writes are via a stored procedure (with same owner), permissions won't be checked at all
  • dbo etc will always have rights (unless you use a trigger, but dbo can disable or drop it)

Upvotes: 1

Related Questions