Table Permissioning

Question

Table Permissioning

sqlnaive

SSCoach

Points: 17437
More actions
August 20, 2012 at 5:18 am

#266209

Is there a way to create a role having insert/update/delete permissions on each table in a database in such a way that every time a new table is created, it's under the scope of that role for the mentioned operations ?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

That would be the db_datawriter built in role. Works as you want, insert, update, delete rights on all tables.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlnaive SSCoach Points: 17437 More actions · Answer 2

Yeah. Sorry but I forgot to mention that due to certain restrictions we cannot use built in roles. Is there kind of parallel mechanism to implement db_datawriter ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Why on earth not use the built ins?

You could create a role that grants the permissions on the schema, then any tables added to the schema get the permissions

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

That's a strange restriction. Are you at liberty to explain? One way round it is to grant INSERT, UPDATE and DELETE on the schema(s) that contain your tables.

http://msdn.microsoft.com/en-us/library/ms187940.aspx

John

sqlnaive SSCoach Points: 17437 More actions · Answer 5

Thats what I was thinking. However then my mind started thinking ridicously for the following command:

grant insert, update, delete on all tables to <rolename>

Nothing sort of this, right ??? 🙂

sqlnaive SSCoach Points: 17437 More actions · Answer 6

The following thing works for me for granting execute permissions on all the procedures to a role:

grant EXEC to <rolename>

The same way i was thinking and thats how my mind started dreaming.:-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

sqlnaive (8/20/2012)
Nothing sort of this, right ??? 🙂

Did you try it?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlnaive SSCoach Points: 17437 More actions · Answer 8

No Gail, Didn't tried it yet. But definitely will do.