IS_SRVROLEMEMBER returning NULL for valid non windows user

Question

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.

Answer 1

I think you're confusing logins and users

• logins exist in sys.server_principals at the Instance level
• users exists in sys.database_principals in each database

Your EXECUTE AS is executed based in info in sys.server_principals but requires an entry in sys.database_principals.

Here, I have a login gbntest that maps to user gbntestU in my GBN database. My stored procedure works OK

USE master
GO
CREATE LOGIN gbntest WITH PASSWORD = 'pwd'
GO
USE GBN
GO
CREATE USER gbnTestu FROM LOGIN gbntest;
GO

--0, OK
DECLARE @loginuser varchar(100) = 'gbntest';
SELECT IS_SRVROLEMEMBER('sysadmin'), IS_SRVROLEMEMBER('sysadmin', 'JoeBlow'), IS_SRVROLEMEMBER('sysadmin', @loginuser);
GO
--NULL, expected
DECLARE @loginuser varchar(100) = 'gbntestu';
SELECT IS_SRVROLEMEMBER('sysadmin'), IS_SRVROLEMEMBER('sysadmin', 'JoeBlow'), IS_SRVROLEMEMBER('sysadmin', @loginuser);
GO

CREATE PROC dbo.uspGBNTest @loginuser varchar(100)
AS
SELECT IS_SRVROLEMEMBER('sysadmin'), IS_SRVROLEMEMBER('sysadmin', 'JoeBlow'), IS_SRVROLEMEMBER('sysadmin', @loginuser);
GO
EXEC dbo.uspGBNTest 'gbntest' -- get 0, OK
EXEC dbo.uspGBNTest 'gbntestu'  --get NULL, expected
GO

So, what do these give in the stored proc?

SELECT
  SUSER_ID(@loginUser), -- sys.server_principals
  DATABASE_PRINCIPAL_ID(@loginUser) -- sys.database_principals (for completeness)

This will tell you if

• @loginuser is set correctly For example, TheUser is sent when it should be DOMAIN\TheUser
• the expected principal (@loginuser) exists at both the Instance and database level for IS_SRVROLEMEMBER to work