Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Table Permissioning Expand / Collapse
Author
Message
Posted Monday, August 20, 2012 5:18 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 10, 2014 7:07 AM
Points: 3,545, Visits: 2,654
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 ?
Post #1347089
Posted Monday, August 20, 2012 5:19 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:09 AM
Points: 40,172, Visits: 36,563
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 2008, MVP
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

Post #1347092
Posted Monday, August 20, 2012 5:23 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 10, 2014 7:07 AM
Points: 3,545, Visits: 2,654
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 ?
Post #1347096
Posted Monday, August 20, 2012 5:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:09 AM
Points: 40,172, Visits: 36,563
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 2008, MVP
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

Post #1347100
Posted Monday, August 20, 2012 5:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:51 AM
Points: 5,416, Visits: 10,070
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
Post #1347101
Posted Monday, August 20, 2012 6:02 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 10, 2014 7:07 AM
Points: 3,545, Visits: 2,654
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 ???
Post #1347111
Posted Monday, August 20, 2012 6:09 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 10, 2014 7:07 AM
Points: 3,545, Visits: 2,654
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.
Post #1347118
Posted Monday, August 20, 2012 6:58 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:09 AM
Points: 40,172, Visits: 36,563
sqlnaive (8/20/2012)
Nothing sort of this, right ???


Did you try it?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1347145
Posted Monday, November 5, 2012 3:54 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 10, 2014 7:07 AM
Points: 3,545, Visits: 2,654
No Gail, Didn't tried it yet. But definitely will do.
Post #1380963
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse