November 10, 2011 at 11:28 pm
How can i check that which user has insert, update and delete permissions on which tables in my database ? Is there some script that can make my work easier ?
November 11, 2011 at 1:52 pm
Look up sp_helprotect in BOL. It should return what you're looking for. But keep in mind that it "does not return information about securables that are introduced in SQL Server 2005" (source: BOL).
November 11, 2011 at 4:31 pm
I would cease and desist with sp_helprotect if you're managing anything beyond SQL Server 2000.
You're wanting to look at the following catalog views and tie them together appropriately:
- sys.database_permissions
- sys.database_principals
- sys.objects
- sys.schemas
Keep in mind that with the concept of securables in SQL Server 2005 and above also comes the concept of scopes. A scope is a securable that can contain other securables. So if you're not familiar with what a securable is, it's basically anything in SQL Server you can assign a permission against. A table is a securable as is a stored procedure. An example of a scope would be the schema that contains the table or the database which contains the schema (so you do have nesting going on).
The reason I say junk sp_helprotect is with the new securables, if you have permission at the scope level, you have permission for any securables within that scope for which the permission applies. So if you think about what I just said, you'll need to look for:
- SELECT, INSERT, UPDATE, DELETE at the object level
- SELECT, INSERT, UPDATE, DELETE at the schema level
- SELECT, INSERT, UPDATE, DELETE at the database level
Only sys.database_permissions will give you all that, and it has columns corresponding to class to tell you if the permission is against the object, schema, or database level.
K. Brian Kelley
@kbriankelley
November 14, 2011 at 12:36 am
Thanks LutzM and Brian for your inputs. I will go through these views and try to get the information I am looking for.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply