1

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.

2
  • What does SUSER_ID(@loginUser) give? This will test if @loginUser exists at the server level. EXECUTE AS is at the database level here.
    – gbn
    Commented Aug 2, 2011 at 21:25
  • Just a suggestion that you may have already considered. Could you possibly implement impersonation on the service?
    – Chris Diver
    Commented Aug 2, 2011 at 23:24

1 Answer 1

1

I think you're confusing logins and users

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
4
  • sUser_Id('loginUser') returns a value that matches the principal_id of the loginUser in the sys.server_principals. Also, as you mentioned above, I checked sys.server_principals and sys.database_principals and both have an entry for loginuser
    – summerboy
    Commented Aug 2, 2011 at 21:46
  • @user428468: I can only guess that the loginuser parameter is not what you expect? Does SUSER_ID work in the proc?
    – gbn
    Commented Aug 2, 2011 at 21:48
  • SUSER_ID and DATABASE_PRINCIPAL_ID both return values when used in the stored proc
    – summerboy
    Commented Aug 3, 2011 at 0:54
  • @summerboy: so IS_SRVROLEMEMBER should work then Try EXECUTE AS OWNER at the top of the stored procedure now please
    – gbn
    Commented Aug 3, 2011 at 5:16

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.