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

Check execute permissions on stored procedure ? Expand / Collapse
Author
Message
Posted Monday, February 6, 2006 4:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 15, 2007 3:58 AM
Points: 3, Visits: 1

Is there a way to check the list of users who have execute permissions on a stored procedure ?

Thanks,

 

Post #256214
Posted Monday, February 6, 2006 8:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:02 AM
Points: 168, Visits: 330
Hi Farha Saleem,

You can open the properties dialog in Enterprise Manager and click "Permissions..."

or, you can do it with a query (you'll have to add the schema portion if you need that)...
SELECT su.name, -- user name with permission
so.name -- procedure name
FROM syspermissions AS sp
INNER JOIN sysobjects AS so
ON sp.id = so.id
INNER JOIN sysusers AS su
ON sp.grantee = su.uid
WHERE so.name = 'procedure_name'

Post #256241
Posted Tuesday, February 7, 2006 12:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 20, 2012 12:51 PM
Points: 322, Visits: 351

Hi.  Your best bet is to use the "sp_helpprotect" system stored procedure.  Here is the syntax (from Books Online):

sp_helprotect [ [ @name = ] 'object_statement' ]
    [ , [ @username = ] 'security_account' ]
    [ , [ @grantorname = ] 'grantor' ]
    [ , [ @permissionarea = ] 'type' ]

In your case, you would use:

"sp_protect 'stored_procedure_name'"

 

For these types of questions, books online is by far the best resource there is.  In fact, if you click the index tab and type in sp_helpprotect, it will go into a lot more detail about its use and what not.  I hope this helps.

Thanks,

Eric




Post #256265
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse