1

Is there a way to deny delete permissions for set of tables, for a particular user in sql server 2008 .i want that a user can not delete data from some tables in a database , do not want to use instead of trigger for particular table cause i have 90 tables ??

P B
  • 67
  • 1
  • 9
  • Check here: http://stackoverflow.com/questions/2988741/how-to-give-over-right-table-permission-in-sql-server-2008 – psur Nov 30 '12 at 10:28

1 Answers1

7

Absolutely!

DENY DELETE ON (YourTableNameHere) TO YourUserNameHere

See the wonderful and extensive MSDN Books Online docs for more details!

If you want to deny the DELETE permission on all tables in a database, you can use:

DENY DELETE TO YourUserNameHere

Or if you've structured your database in such a way that the tables are grouped in schematas, then you can also deny a user from a specific schema.

If you haven't grouped your 90 tables into a separate schema (or two, three schematas), then there's no easy, magical way to apply a permission to 90 tables at once. It's either all tables, a specific schema, or a specific database object (like a table) per DENY statement.

Update: you can always use the system catalog views to generate those DENY statements for you, and then use those that you need:

SELECT 
    'DENY DELETE ON ' + t.NAME + ' TO (youruser)'
FROM sys.tables t

This will produce as its output (e.g. in SQL Server Management Studio) a list of statement to deny the DELETE permission from your user. Copy those rows into a text editor and remove those rows you don't need - and there you have your long list of DENY statements!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • this will be for 1 table and i have more then 90 tables – P B Nov 30 '12 at 10:29
  • ya but i want any other way to do so ,not writing 90 statements for 1 database , then for other database and so on.... – P B Nov 30 '12 at 10:31
  • @puneet: you can issue `DENY` for **all** tables, for a specific schema, or a specific table. You cannot do it any other way. – marc_s Nov 30 '12 at 10:32
  • i have tables with same schema name and i don't want to deny permission to delete from all tables, – P B Nov 30 '12 at 10:36
  • @puneet: well, then do `DENY DELETE ON (schemaname) TO (username)` for those schematas (one at a time) that you want to deny your user from – marc_s Nov 30 '12 at 10:38