question

user-463 avatar image
user-463 asked

Deny update on all tables for user

Is there a way to deny a right for all tables in a database without explicitly defining each table in the command? My problem is that I have a user who is assigned to the public role, and should only be able to read the tables. But if I look at the table permissions this user has update granted to him as well. I am able to deny update to the tables by typing them out using the command: DENY UPDATE ON TO

What I would like to have is something that would allow me to deny update on all tables, but I don't know if this is possible using the above approach, or if this is scriptable. Any suggestions are appreciated.

Kurt Kapferer

updatepermissions
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

BlackHawk's answer is good, but you may want to investigate why he has update granted. Most likely he is inheriting from one of the groups he is in (assuming this is a Windows login). In that case, you may wish to track it down and see if he really needs to be in that group.
2 Likes 2 ·
Blackhawk-17 avatar image
Blackhawk-17 answered

You could add the user to the db_denydatawriter dB role.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

Try a schema level permission.

If all the tables are in the dbo schema, then

DENY UPDATE on SCHEMA::dbo TO user

even if you have multiple schemas, it's surely a lot less than the number of tables?

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

I agree with the other guys, but - just to show that you should never have to type a load of stuff manually, here is some SQL that writes some SQL.

DECLARE @sql varchar(MAX)
SET @sql = ''
SELECT @sql = @sql + 'DENY UPDATE on OBJECT::[' + SCHEMA_NAME(schema_id) + '].[' + name + '] TO user
GO
' from sys.objects where type = 'u'
EXEC (@sql)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.