Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 Aug 2 '11 at 21:25
    
Just a suggestion that you may have already considered. Could you possibly implement impersonation on the service? –  Chris Diver Aug 2 '11 at 23:24
add comment

migrated from stackoverflow.com Jan 22 '12 at 13:04

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 0 down vote accepted

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
share|improve this answer
    
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 Aug 2 '11 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 Aug 2 '11 at 21:48
    
SUSER_ID and DATABASE_PRINCIPAL_ID both return values when used in the stored proc –  summerboy Aug 3 '11 at 0:54
    
@summerboy: so IS_SRVROLEMEMBER should work then Try EXECUTE AS OWNER at the top of the stored procedure now please –  gbn Aug 3 '11 at 5:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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