December 6, 2011 at 6:58 am
Hello DBAs, I have 100 tables in my database. Out of which 20 are master tables, 40 are transaction tables and rest are for auditing purpose. I have one group G_WriteTables. I want to give read permissions to this group for all the tables and write permissions only for 20 master tables. Is there a better way than giving inert, update, delete permissions individually for each table ?
December 6, 2011 at 7:11 am
You could put the different types of table in different schemas and then grant permissions on the schemas.
John
December 6, 2011 at 7:32 am
Can I use implementing both db_datawriter and db_denydatawriter for the solution ?
December 6, 2011 at 7:36 am
No. From Books Online:
Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
Note the "all". You need to grant permissions on schemas or on individual tables. A third option is to revoke all direct access to tables, and have all access via stored procedures, on which you grant EXECUTE permission. This is the most secure solution, although it's also the hardest work.
John
December 6, 2011 at 7:41 am
you could create a role, put the users in the role, grant the access needed to that role, that way you can add and remove users as you wish and dont need to keep messing with permissions every time you add or remove a user
giving both db_datawriter and db_denydatawriter will only actually give db_denydatawriter as DENY takes presidnet over GRANT, so you could say
GRANT SELECT ON SCHEMA::DBO TO [Anthony.Green]
then do
DENY SELECT ON SCHEMA::DBO TO [Anthony.Green]
And even though you have allowed me to select, I cant as you have also said that I cant select.
December 9, 2011 at 6:53 am
anthony.green (12/6/2011)
you could create a role, put the users in the role, grant the access needed to that role, that way you can add and remove users as you wish and dont need to keep messing with permissions every time you add or remove a usergiving both db_datawriter and db_denydatawriter will only actually give db_denydatawriter as DENY takes presidnet over GRANT, so you could say
GRANT SELECT ON SCHEMA::DBO TO [Anthony.Green]
then do
DENY SELECT ON SCHEMA::DBO TO [Anthony.Green]
And even though you have allowed me to select, I cant as you have also said that I cant select.
Actually, the way this works the DENY replaces the GRANT (there will only be one entry for the permission against the object, not two). With respect to db_datawriter and db_denydatawriter, the db_denydatawriter will take priority, as Anthony indicated because DENY trumps.
The previous suggestion, to create a role, grant permissions against the individual objects, and make users a member of the role is pretty much the accepted best practice for how to handle permissions in general. It also requires 0 re-work from a reports/app/ad hoc querying perspective. If you could isolate the tables into a single schema, then it would be preferable to do so, because they you can simply grant the permissions to a role against the whole schema. If that's not doable, then do the individual permissions against the role.
K. Brian Kelley
@kbriankelley
December 22, 2011 at 4:29 am
Thank you. I am looking to pinpoint on the idea of having a role with necessary permissions on required objects. Seems best as per our requirement.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply