denay delete for all spesifc sql server logins ID

Question

Hi ,

I need to deny delete permission for specific number of SQL Server login on multiple databases all tables

kindly note some of those users have write\read permission ?? doable ..

also is there a way to develop a policy whoever joined to this policy won't be able to perfrome any of delete statement on any of particular databases 

Answer 1

Hi,

You can create a database role and assign the permissions to that role. Then you need to add the users to that database role.

Have a look at below link to see some similar example

http://beyondrelational.com/modules/2/blogs/88/posts/10181/sql-server-custom-database-role.aspx

---

- Chintak (My Blog)

Answer 2

thanks for your reply

can i create a global role on the instance level

Answer 3

Just do not those logins grant access to those databases.

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 4

Hi,

If you do not want to create many different logins,  if you are using windows authentication, you can create a group in active directory for all such logins and create SQL login from the AD group.

But, even this way you need to create the database level role for each database and create user for that login and add user to this DB role.

---

- Chintak (My Blog)

Answer 5

DENY DELETE is a database level permission, so it cannot be assigned to a server level role. Only to a database level role or user. So you have to recreate the role in each database.

However, DELETE permission isn't automatically granted to anyone. It could be useful to figure out how people got the DELETE permission in the first place, and then change that. By that I mean, that new server logins and new database users don't have DELETE permission unless you grant it to them. Why did you grant it to them if they should not have it? Probably because some other (overly general) permission was granted. So that's what you need to change. Be more specific on whatever that grant was.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty