Find if Windows User belongs to a windows group for a SQL server?

[RustyAfro]

Question:
I cannot figure out how to determine if a Domain/UserID is in a Windows Group for a SQL Server database that has Windows Authentication.

Details:
I have tried searching for days how to do this and figure I must be phrasing it wrong in search engines to find the answer. I am hoping someone here can help =).

I have a SQL Server (2k) that has 3 user groups set up that "point?" to it. If I go into Enterprise Manager and click on the Security folder -> Logins, I can see the groups.

For example, here is one group:
Name: Tier1_User
Type: Windows Group
Server Access: Permit
Default Database: MyDatabase

The database uses Windows Authentication. I am using VB.net and I know how to get the Domain/UserID that the user is logged in with.

What I cannot figure out is how to determine if the Domain/UserID is in the Windows Group. I have found a Win32 statement that can pull in the group, but as yet found nothing about users in the groups.

If anyone has code, SQL, or information to point me in the right direction I would really apreciate it ;-).

Thanks,

Joel

 

[mrdenny]

Try this.

exec master.dbo.xp_logininfo '{Domain}\{UserName}', 'all'

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[RustyAfro]

It's look like that would work, but I am denied privileges to xp_login info =(

I did find something for vb.net, which is below and it returns a boolean true/false to indicate if the person is in a group. I would rather find this on the SQL server though, so if you have any other ideas I will try them =).


Imports System.Security.Principal
Imports System.Threading

Sub main()
AppDomain.CurrentDomain.SetPrincipalPolicy(PrincipalPolicy.WindowsPrincipal)

MsgBox(Thread.CurrentPrincipal.IsInRole("HNCORP\Tech_Admin"))

End Sub

 

[SQLBill]

Basically, SQL Server doesn't know who is in what Domain Groups. When you use Windows Authentication, SQL Server let's the Operating System (Windows) handle the authentication....SQL Server 'trusts' Windows to get it right. All SQL Server cares about is that you have the Windows Group in SQL Server logins.

-SQLBill

Posting advice: FAQ481-4875

 

[is47501]

If I understand what you need this may work. I created it as a stored procedure and call the sp from VB. You may be able to find a way to execute only the SQL. One of the main keys though is the server settings in the first comment.

create PROCEDURE dbo.csp_getUserApp2
--********************************************
--** To create this stored procedure The following
--** needs to be run against the master DB
--**
--** EXEC sp_configure 'allow updates', 1
--**    RECONFIGURE WITH OVERRIDE
--**
--*********************************************
@wingrp varchar(50)
as
Declare @user as varchar(15)
Select @user = sUSER_sName()

SELECT DISTINCT CASE WHEN ISNULL((select distinct
s.NAME From
OpenRowset(NetUserGetGroups,@user) n
INNER JOIN master.dbo.syslogins s on n.sid = s.sid
WHERE s.NAme = @wingrp),'1') = '1' THEN 1 ELSE 0 End

GO


csp_getUserApp2 'PCSHS\sqldmqa'


You can of course make modifications if you need to pass the User Id.