Share via

Deleting securables and DB roles

  • Article

Question

Tuesday, September 20, 2011 8:00 PM

I would like to know how it is working, I have 2 database level roles with a whole bunch of securable attatched to them.
I just when thru some improvements in the db and realize that some of the already created views are no longer needed.
If I delete these useless views, are the going to be removed from the database level roles too or do I need to go thru them and remove them all ?

I just would like to know before I do anything wrong

Thanks

All replies (3)

Tuesday, September 20, 2011 9:45 PM âś…Answered

Securables aren't "attached" to roles. I assume you mean that these two roles have been granted or denied permissions to the securables. When a securable is dropped, the permission statements related to that securable are automatically removed from sys.database_permissions.Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Tuesday, September 20, 2011 9:50 PM

I'm not really sure that I understand your question, because you cannot attach a role to a view - do you mean that you have granted permissions to the view?

In any case, if you drop an object, for instance a view, and there are references to the object in the system catalog, those references are either dropped as well, or SQL Server will tell you that the object cannot be dropped. In no case, it will leave you with an orphaned reference.

Whether the drop is cascaded or not is in many cases quite obvious. If you have granted permissions, the permissions are dropped. But if you have defined an indexed view and try to drop one of the tables defined in the view, you will get an error.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Wednesday, September 21, 2011 1:34 PM

Thanks for the info, sorry if I was not quite clear, I really meant granting permissions to securable thru dadabase roles