Sql-server – How to prevent sysadmin server role from executing a stored procedure

permissionsrolesql serversql-server-2012

I have a SQL Server default instance using SQL Server Authentication and two users, User1 and User2.

User1 has the public server role while User2 has the public server role + sysadmin server role.

I have created one database, sptest1. Inside that database I have created a stored procedure, sp_viewtbl and also created a schema. Using the schema I have disabled executing stored procedure for User1 and it works.

How can I deny executing the stored procedure by User2, who has sysadmin server role?

Suppose I have created User3 and given permissions as sysadmin and public.
So how can I prevent that new user from executing that same stored procedure? I have to secure sp_viewtbl from others login even if they have sysadmin permission.

Best Answer

Members of the sysadmin role cannot be denied permission to anything in SQL Server. The documentation for DENY (Transact-SQL) states:

...except that DENY does not apply to object owners or members of the sysadmin fixed server role.

So in order to prevent a sysadmin from running a stored procedure, you would need to put code in the stored procedure to check if the user is a sysadmin and return an error if they are. However, a sysadmin could easily get around this by:

  • modifying the stored procedure
  • creating a new stored procedure with the same code and ripping out the part that checks for sysadmin
  • running the same code, minus the part that checks for sysadmin, from a SQL client
  • using EXECUTE AS to run the procedure as a different user

If your goal is to prevent sysadmin users from seeing sensitive data, the way to do that is to use Always Encrypted. This encrypts and decrypts the data with a key that only the application has access to. If a sysadmin reads the table, they will just see encrypted data.

Related Question