5

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.

Improve this question
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
    Commented Apr 23, 2020 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
    Commented Apr 23, 2020 at 10:21
  • 6
    The solution is to remove user2 and user3 from the sysadmin role.
    – Dan Guzman
    Commented Apr 23, 2020 at 10:45
  • There is no other option to change permission of sysadmin server role ?
    – user3771136
    Commented Apr 23, 2020 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
    Commented Apr 23, 2020 at 10:49

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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