Accessing Windows Users assigned to a database role

Is there a way to find a list of Windows User accounts that are directly or indirectly (through Windows Group membership) assigned to a database role?
I could put work in to CLR programming or using a Linked Server to Active Directory, but if there is a sys.* view available that can provide me this information directly it would be much easier.
I'll be looking into this further myself anyway and posting an answer if I can find one, but if anyone has suggestions... well, thanks!

Hi,

guess not, but if you have one ready, feel free to post it to me, I will bring it online on my soon launched site www.sqlserver2005.de

HTH, jens Suessmeyer.

Accessing sys.syslockinfo from an application role

In SQL Server 2005, you must have the VIEW SERVER STATE permission in order to access sys.syslockinfo (http://msdn2.microsoft.com/en-us/library/ms189497).

It seems that the VIEW SERVER STATE permission can only be granted to users.

Once you execute sp_setapprole, the connection loses the permissions of the user and assumes the permissions of the application role (http://msdn2.microsoft.com/en-us/library/ms190998).
So, how can I access the sys.syslockinfo view while using an application role?
Note: I have to maintain compatibility with SQL Server 2000

Here are two ways to allow an application role access to syslockinfo:

a) Create a signed stored procedure that exposes syslockinfo and grant execute on it to the application role. An example of procedure signing has been presented in the following post:

controlling security through stored procedures -- 2005 behaviour

b) Grant VIEW SERVER STATE to public.

Thanks
Laurentiu|||

Thanks for your help. I see that they just added this knowledge base article: http://support.microsoft.com/kb/906549, which has another example. Did you write the kb article?

|||Thanks for posting the KB link, this is recent indeed. No, I didn't write that article but I see it's a complete description of the solution I mentioned as (a). Let us know if you have any trouble with that solution.

Thanks
Laurentiu|||Their example works great for syslogins and sysprocesses. However, it doesn't work for syslockinfo.

This is the error message:
The user does not have permission to perform this action.|||Nevermind, I think there was just something wrong with my install. I reinstalled, and now it is working. Thanks again for your help.

Accessing stored procedure with another role?

Hi NG,
I have the problem that I need to give certain users the right to add and
remove other users from roles. I have a stored procedure that looks
simplified like this
CREATE PROCEDURE UpdateUserRoles
(
@.UserName varchar(255),
@.RoleName varchar(255),
)
AS
EXEC sp_addrolemember @.RoleName, @.UserName
GO
This works for me fine because I have the db_owner role, but if you don't
have the role you are not allowed to execute the system sp
"sp_addrolemember". And I need that some users of the programm can execute
this procedure, but I can grant them all rights there are, they habe to be
in the db_owner role and I don't want to do that for obvious reasons.
My question, is it possible, that everybody who ist allowed to use this
procedure is allowed to use it as db_owner? I don't want them to be in the
db_owner role, they should just have the right for this one procedure...
I tried:
GRANT Execute ON UpdateUserRoles to User as db_owner
but I got
Grantor does not have GRANT permission...
Any help is appreciated...Christian
I've got
Server: Msg 15247, Level 16, State 1, Procedure sp_addrolemember, Line 49
User does not have permission to perform this action.
I'm affraid you cannot. However , please take a look at an Application Role
that you can activate on the connection to the database
"Christian" <uce@.cash4banners.de> wrote in message
news:u6gw5jLnFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Hi NG,
> I have the problem that I need to give certain users the right to add and
> remove other users from roles. I have a stored procedure that looks
> simplified like this
> CREATE PROCEDURE UpdateUserRoles
> (
> @.UserName varchar(255),
> @.RoleName varchar(255),
> )
> AS
> EXEC sp_addrolemember @.RoleName, @.UserName
> GO
> This works for me fine because I have the db_owner role, but if you don't
> have the role you are not allowed to execute the system sp
> "sp_addrolemember". And I need that some users of the programm can execute
> this procedure, but I can grant them all rights there are, they habe to be
> in the db_owner role and I don't want to do that for obvious reasons.
> My question, is it possible, that everybody who ist allowed to use this
> procedure is allowed to use it as db_owner? I don't want them to be in the
> db_owner role, they should just have the right for this one procedure...
> I tried:
> GRANT Execute ON UpdateUserRoles to User as db_owner
> but I got
> Grantor does not have GRANT permission...
> Any help is appreciated...
>|||Thanks Uri.
Changing shortly the connection to another user worked for me. Thanks
again...
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:uZ2KtuLnFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Christian
> I've got
> Server: Msg 15247, Level 16, State 1, Procedure sp_addrolemember, Line 49
> User does not have permission to perform this action.
>
> I'm affraid you cannot. However , please take a look at an Application
> Role that you can activate on the connection to the database
>
>
> "Christian" <uce@.cash4banners.de> wrote in message
> news:u6gw5jLnFHA.3656@.TK2MSFTNGP09.phx.gbl...
>