CODEWITHSUNDEEP
sqlsql-server
CoderBrien
CoderBrien

Reputation: 693

SQL Server : prevent updates to a table via user permissions

I have never worked with users/logins in SQL Server and am having a hard time getting a grasp of it.

I am currently logging in as Administrator which sets me up as the "dbo" database user.

There is a specific table that I want to prevent UPDATES to. I tried altering permissions via 

DENY UPDATE on SCHEMA::table TO dbo

but apparently you can't mess with the dbo user's permissions.

So I create a new user myuser and switched via setuser myuser but that user doesn't seem to have permissions to even select from the tables.

How should I grant "everything except update a specific table" permissions to this user?

Upvotes: 1

Views: 3463

Answers (1)

Martin Smith
Martin Smith

Reputation: 453910

To grant "everything except update a specific table" permissions you can do

EXEC sp_addrolemember N'db_datareader', N'myuser'

EXEC sp_addrolemember N'db_datawriter', N'myuser'

DENY UPDATE ON dbo.YourTable TO myuser

From BOL

Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

Members of the db_datareader fixed database role can read all data from all user tables.

Upvotes: 3

Related Questions