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

execute procs,data read and data write permission for users Expand / Collapse
Author
Message
Posted Monday, January 31, 2011 6:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:21 AM
Points: 232, Visits: 809
Hi All

I want the user to be able execute procs,data read and data write permission for a database. Vendor is suggesting me to give DBO permission but I am not happy with it as users can drop the database as well.

Is there a way I can only give execute procs,data read and data write permission for users?

Thanks

Post #1056125
Posted Monday, January 31, 2011 6:24 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 @ 1:53 PM
Points: 42,771, Visits: 35,870
db_datareader, db_datawriter roles and then grant execute on the schema that the procedures are in. If it's the dbo schema, then...

GRANT EXECUTE ON SCHEMA::dbo TO <username>




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 #1056136
Posted Thursday, February 3, 2011 11:06 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 11:53 PM
Points: 568, Visits: 71,054
Yeah I take this direction and added a db_role called Exec_SP or db_Exec giving the role the securable and just adding users to the role. To me it seems more transparent.
Post #1058244
Posted Friday, February 4, 2011 7:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:24 PM
Points: 11,235, Visits: 12,988
Call me crazy but I don't like giving db_datareader and db_datawriter permissions. I prefer to create my own custom roles that have only the needed permissions and then assign users to that role. You could do this by assigning rights at the schema level or at the object level. The nice thing, but also the not so nice thing, about schema level permissions is that they apply to any objects created in the schema at any time.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1058759
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse