I have a service that executes a stored procedure to query the db for results. The stored procedure needs to use impersonation on the user who is logged in the client (since the service has its own separate credentials to connect to the db). The name of the user logged in the client is passed as a parameter (@loginUser) to the stored procedure.
If the @loginUser has sysadmin rights in the sql server, the procedure should not do any impersonation and simply proceed to query the table. The impersonation should only be used for users without sysadmin permissions.
To do that, I am using the following statement in the procedure.
DECLARE @HasSysadminRole BIT SELECT @HasSysadminRole = IS_SRVROLEMEMBER('sysadmin', @loginUser) IF @HasSysAdminRole = 0 EXECUTE AS USER = @loginUser ... ... ... IF @HasSysAdminRole = 0 REVERT
The procedure is created on the db using the sa login.
Now, if I execute the procedure with a user who doesn't have sysadmin permissions, I get NULL as the value of IS_SRVROLEMEMBER('sysadmin', @loginUser) even though the @loginUser is a valid user in the db and server.
If I run the same IS_SRVROLEMEMBER('sysadmin', @loginUser) statement in a query window in Sql mgmt studio, logged in as sa, I get the value 0.
I do not know why the sql server is behaving differently in these 2 circumstances.