sql server programming: I'm getting inconsistencies with the function IS_SRVROLEMEMBER. When I
execute the function in a query window, it returns the value I expect. In my
stored procedure, it is returning null for any username that is a windows
account.

I'm using SQL Server 2005 SP2. Windows + SQL Server authentication have
been turned on. The windows roles being checked have been granted sysadmin
role. The function is being called to identify if an account is in the
sysadmin role. I'm passing in the user name I wish to check as a secondary
parameter:

DECLARE @message_body XML,
@message_type_name NVARCHAR(256),
@username sysname,
@admin int,
@rolename sysname,
@eventtype varchar(100),
@dialog UNIQUEIDENTIFIER ;
....

More code
....
SET @rolename = N'sysadmin'
SET @eventtype =
CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100))
SET @username =
CAST(@message_body.query('/EVENT_INSTANCE/LoginName/text()') AS sysname)
SELECT @admin = IS_SRVROLEMEMBER(@rolename, @username)
....

More code
....

This is essentially a stored procedure that is used in event processing
(queue/service/route) defined.

When a SQL Server account is passed in as the username, the function
processes correctly, (returning 1 or 0). When a Windows account name is
passed in as the username, the function returns NULL.

Similar code in a query window returns the correct value for the username.

Hello, Bryme

Here is a quote from Books Online that might give us a clue:

If login is a Windows domain login, it may be a member of a fixed
server role through membership in a Windows group. To resolve
such indirect memberships, IS_SRVROLEMEMBER requests
Windows group membership information from the domain controller.
If the domain controller is not accessible or does not respond,
IS_SRVROLEMEMBER returns role membership information
taking into account the user and its local groups only.

I guess that in your case the login for that Windows user is in fact
defined for a Windows group (and the user is a member of that group),
instead of being defined directly for that Windows user.

Under what account are each of your SQL Server services configured to
run ? (Local Service / Network Service / a local user account / a
domain user account)

Razvan

It's a local windows account that I'm trying to interrogate (I know should be
a domain user) and SQL Server is using a local account as well.

The test server is running as a VM on my laptop, so I didn't create a full
domain structure. I'll try on a domain member server with domain accounts to
interrogate as well as run SQL Server and see if that matters, but it really
shouldn't.

I've tried several more scenarios for IS_SRVROLEMEMBER:

SQL Server checking local accounts and a local account running the SQL service
SQL Server checking domain accounts and a domain account running the SQL
service
SQL Server checking any account and either local system or network service
running the SQL service.

All of these return correctly when run in a query window. For a normal
stored procedure, it also returns correctly. For the stored procedure that
is being called via the queue, it always returns NULL for any calls checking
windows or domain accounts.

OK, after much testing, I'm starting to see what the problem is.

Apparently, if you want to use IS_SRVROLEMEMBER, as long as the user you are
checking is the current user or a SQL Server account, the function will
return the expected value. If I want to check another Windows (local or
domain) user, it looks like I have to have at least the securityadmin role on
the current user to do so.

Here's a test procedure:
CREATE PROCEDURE [dbo].[testRole] (@UserName sysname)
AS SELECT IS_SRVROLEMEMBER('sysadmin', @UserName)

The problem is how to grant any other user the right to run this function?
1. Use the EXECUTE AS LOGIN in a SQL batch. - works, but Impersonation
must be allowed for the user you wish to impersonate and you also get the
rights and abilities of that user - Not Good!
2. Use WITH EXECUTE AS when defining my stored procedure. - can't get
any varioation to work. You'd figure that I would be able to specify that
the execution runs as a user that would have permissions to execute the
function and return the value as expected, but not a chance.
EXECUTE AS SELF and EXECUTE AS OWNER return null values for *ANY* user that
attempt to run the stored procedure. This includes users with the ability to
get the right values for IS_SRVROLEMEMBER.
EXECUTE AS CALLER is the only way to get anyone to be able to return the
right values, but that still is restricted to securityadmin and sysadmin
roles.
EXECUTE AS <user> you'd think would be the key, but not even a chance.
Instead, we get a new error: The server principal <user> is not able to
access the database <database> under the current security context.

Though the last one seems like we are getting somewhere, the suggestions for
resolution don't really help. The only thing that is close is this article:
http://support.microsoft.com/default.aspx/kb/913422. Unfortunately, the
workarounds described, don't work for this situation. What seem to be the
key here is in the cause for this article:

This issue occurs because SQL Server strips down the server-scoped
permissions when you impersonate a database user.

For security considerations, the server-scoped permissions are stripped down
when you impersonate a database user unless the system administrator has
explicitly set SQL Server to trust the impersonated context at the
server-scope. In this case, a login with the control server server-scoped
permission has no permissions to access any particular database. Therefore,
the trigger module that is executed as this login cannot run.

My question is does anyone know how if there is anyway around this
limitation? For the Microsoft support team, would this be something worthy

Hello, bryme

See the following post by Remus Rusanu, a member of the Service Broker
team:
http://blogs.msdn.com/remusrusanu/archive/2006/01/12/512085.aspx

You can also see the following related posts:
http://blogs.msdn.com/remusrusanu/archive/2006/03/01/541882.aspx
http://blogs.msdn.com/remusrusanu/archive/2006/03/07/545508.aspx

The idea is that you need to sign your procedure with a certificate
and give the certificate user server-level priviledges to perform the
needed tasks.

Razvan

Just missed your recommendation when I posted.

Setting the database as TRUSTWORTHY does work. As for signing the procedure
(my preference for security) I'm going to have to determine what are the
permissions to grant to allow the certificate user the least amount of
permissions to run the function.

My thought is that the permissions to run IS_SRVROLEMEMBER and get results
for Windows users still is flawed. From my other post, I found that you need
at least securityadmin role to execute the function and return valid results.
I'm now investigating what are the minimum permission level I need to grant

Got information on how to get this to work - see previous post by Razvan Socol:
http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.programming&mid=f7208502-ff4f-4e6f-ac05-f319e200c1e0&sloc=en-us