SQL Server Deny all users from a table except for one user

Question

Im not too familiar with SQL Server. Can one of you experts please help me with this !

Ive got a table where I want to stop everyone from making changes (Select is fine) except for one user who can read and write to it.

So far Ive got the following to give the user access,

GRANT SELECT, INSERT, UPDATE, DELETE ON [dbo].[RAckNakLog] TO [svc-eR]

How do I stop everyone else from writing to it ?

Solution

just the opposite:

DENY INSERT, UPDATE, DELETE ON [dbo].[RAckNakLog] TO [svc-eR]

If you already granted access to a user you need to revoke them (like delete GRANT or DENY)

REVOKE INSERT, UPDATE, DELETE ON [dbo].[RAckNakLog] TO [svc-eR]

Comments

[nixgadget]

Yeah but thats not what im after. I want to deny all other users from modifying the table. And only user svc-eR to have write access.

[Luis LL]

So you need to create a new ROLE in the database, DENY access to that table and then add all users to the ROLE. you'll still will need to add each new user to the role when creating them.