3 июля 2012 г. 13:59
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
- Изменено SQL Kitchen 3 июля 2012 г. 14:00
3 июля 2012 г. 14:16
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
- Chintak (My Blog)
3 июля 2012 г. 14:32
thanks for your reply
can i create a global role on the instance level
3 июля 2012 г. 14:40
3 июля 2012 г. 14:42
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)
3 июля 2012 г. 14:45
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
- Помечено в качестве ответа Iric WenModerator 24 июля 2012 г. 6:37