asked
how to apply permission
somebody is deleting data in tables in the database. I want to take out delete permission for that login on all the tables.
Just a thought... are you using different schemas within the database? 'cos I have my doubts over the efficacy of both solutions so far proposed in that case...
Why are they deleting data? Is it just an education issue? Assuming that they're deleting data for no particularly good reason, then you'll want to [REVOKE][1]. Or, if the user is a member of a group that also has delete privs, then use [DENY][2]. Something like: DENY DELETE ON < > TO < > If you want to do this for all tables, then you could wrap it up with the `sp_msforeachtable` stored procedure. sp_msforeachtable 'DENY DELETE ON ? TO < >' [1]:
http://msdn.microsoft.com/en-us/library/ms187728.aspx [2]:
http://msdn.microsoft.com/en-us/library/ms188338.aspx
+1 - but just to add a caveat that the `sp_msforeach...` procs are unsupported.
@ThomasRushton + @WilliamD - Hell, I use it all over the place and would have a shed-load of work to create work-arounds if it got removed but what I 'do' isnt always best practice. Here, however, I like to appear to follow the rules....
How about doing the same as @ThomasRushton suggests, just without the unsupported `sp_msforeach`: DECLARE @User varchar(255), @TSQL varchar(MAX) SELECT @User = 'YourBadUser', @TSQL = '' SELECT @TSQL = @TSQL + 'DENY DELETE ON ' +QUOTENAME(s.name)+'.'+ QUOTENAME(t.name) + ' TO ' + QUOTENAME(@user)+';'+CHAR(10)+CHAR(13) FROM sys.tables T INNER JOIN sys.schemas S ON T.schema_id = S.schema_id WHERE type = 'u' PRINT @TSQL --EXEC (@TSQL)
+1 for a much quicker alternative, and one that's more conducive to being updated should it be found that tables are spattered through various schemas...
@WilliamD - dude, you missed the '.' between schema & tablename!
