Isolating Stored Procedures

Here is a cool way to add an extra level of security to any stored procedures that you wish to isolate from other standard database users. It prevents anyone except (in this case) sysadmin from executing the stored procedure - any attempts from a non sysadmin would result in:

Server: Msg 15247, Level 16, State 1, Line 3
User does not have permission to perform this action.

Example:

IF (NOT (IS_SRVROLEMEMBER('sysadmin') = 1))
BEGIN
RAISERROR(15247,-1,-1)
RETURN
END

PRINT 'Hello, World!'

/* rest of code goes here */

You can of course check for any of the fixed server roles, which are listed below:

• sysadmin
  
• dbcreator
  
• diskadmin
  
• processadmin
  
• serveradmin
  
• setupadmin
  
• securityadmin