Check execute permissions on stored procedure ?

• Farah Saleem

  SSC Veteran

  Points: 257

  More actions

  February 6, 2006 at 4:17 pm

  #90536

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

  Thanks,

   

• Ken Johnson-162971

  Ten Centuries

  Points: 1296

  More actions

  February 6, 2006 at 8:53 pm

  #619148

  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'

• Strommy

  SSCrazy

  Points: 2386

  More actions

  February 7, 2006 at 12:01 am

  #619167

  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