4

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.

7
  • 5
    Permissions for sysadmin role members are not checked so you can't prevent them from doing anything on the instance. One should never use sysadmin role members for routine database access and instead use a proper minimally privileged account. – Dan Guzman Apr 23 '20 at 9:47
  • Sir,you understood my problem.I have to prevent login that has sysadmin privilege from executing particular stored procedure.There is no way to prevent object like stored procedure from login having sysadmin server role.Thanks in advance.please,help me asap. – user3771136 Apr 23 '20 at 10:21
  • 6
    The solution is to remove user2 and user3 from the sysadmin role. – Dan Guzman Apr 23 '20 at 10:45
  • There is no other option to change permission of sysadmin server role ? – user3771136 Apr 23 '20 at 10:47
  • 3
    As I mentioned in my first comment, permissions for sysadmin role members are not checked at all. You can change permissions as you like but it will have no effect. You could hard-code some logic in the proc so it errors if executed by those users but, as a sysadmin role member, they could change the proc to remove it. – Dan Guzman Apr 23 '20 at 10:49
14

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.