Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Access that can allow to execute SP (create object), however not allow to create/drop/alter table from query window Expand / Collapse
Posted Thursday, February 24, 2011 6:51 AM


Group: General Forum Members
Last Login: Tuesday, September 16, 2014 3:32 AM
Points: 24, Visits: 211
I need to give an access right to a user that can execute a SP (which creates object tables), however that access shouldn't allow him to create tables from the query window.

To conclude, As a DBA what kind of access can I give to the user that can allow him to execute SP(which creates object), but at the same time restricts him to create/alter or drop tables from the query window?

Post #1068903
Posted Thursday, February 24, 2011 9:15 PM


Group: General Forum Members
Last Login: Monday, February 8, 2016 4:13 PM
Points: 145, Visits: 202

I can't for the life of me remember the term for doing this but if you grant the stored proc create, insert, update, etc then don't provide the users with any of those rights except execute on the stored proc that should do it for you.
Post #1069383
Posted Friday, February 25, 2011 8:29 PM


Group: General Forum Members
Last Login: Wednesday, November 30, 2016 2:47 PM
Points: 1,869, Visits: 3,728
There are two ways you can achieve this (they can be combined into one):

EXECUTE AS clause:

Module signing:

Have a look at the above links and post further on this thread with any additional questions.

Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1069979
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse