Access that can allow to execute SP (create object), however not allow to create/drop/alter table from query window

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 High Availability » Access that can allow to execute SP (create...

Access that can allow to execute SP (create object), however not allow to create/drop/alter table from query window

Rate Topic

Display Mode

Topic Options

Author

Message

asir83

Posted Thursday, February 24, 2011 6:51 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:02 AM
Points: 21, Visits: 167

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?

Regards
Asir

Post #1068903

sean.massey

Posted Thursday, February 24, 2011 9:15 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 19, 2013 9:32 PM
Points: 132, Visits: 175

Hi,

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

Marios Philippopoulos

Posted Friday, February 25, 2011 8:29 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:10 AM
Points: 1,824, Visits: 3,477

There are two ways you can achieve this (they can be combined into one):

EXECUTE AS clause:
http://msdn.microsoft.com/en-us/library/ms178106.aspx

Module signing:
http://msdn.microsoft.com/en-us/library/ms178106.aspx

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

__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation

Post #1069979

« Prev Topic | Next Topic »

Permissions