Checking Windows User Group

Question

Checking Windows User Group

ArnolddG

SSC-Addicted

Points: 464
More actions
June 7, 2015 at 11:24 am

#398556

Hi,
I am setting up security for access of database tables for members in a specific Windows User Group.
I want to check in a SQL script if this Windows User group is added and if so, add database users and grant SELECT on specific tables.
I have tried this:
SELECT * FROM master.sys.syslogins WHERE name like '%FoeUsers' AND isntgroup = '1'
...but that selects a SQL user or group and not a Windows Group.
Is there a way to check if a Windows NT (active Directory) user group exists?
If not, can this group be added in a script?
Thanks,
Arnold

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

TomThomson SSC Guru Points: 104773 More actions · Answer 1

ArnolddG (6/7/2015)
Hi,
I am setting up security for access of database tables for members in a specific Windows User Group.
I want to check in a SQL script if this Windows User group is added and if so, add database users and grant SELECT on specific tables.
I have tried this:
SELECT * FROM master.sys.syslogins WHERE name like '%FoeUsers' AND isntgroup = '1'
...but that selects a SQL user or group and not a Windows Group.
Is there a way to check if a Windows NT (active Directory) user group exists?
If not, can this group be added in a script?
Thanks,
Arnold

Assuming you know the domain name, you can find all windows logins and groups that are logins to the SQL Server instance using code like

select * from master.sys.syslogins where name like 'MyDomain\%' ;

If you also know the groupname, change that to

select * from master.sys.syslogins where name like 'MyDomain\GroupName' ;

(using your domain name and group name , not "<MyDomain" and "GroupName", of course).

But I think there's a better method:

If you run

CREATE LOGIN [MyDomain\GroupName] FROM WINDOWS WITH DEFAULT_DATABASE=[MyDB], DEFAULT_LANGUAGE=[MyLanguage] ;

it should raise error 15025 is the server principal already has an SQL login, and create the login if it doesn't. This certainly works in SQL 2014, and although I don't have earlier Sql 2012 systems with me right now to check it I believe it was the same from SQL 2008 R2 onwards, and perhaps even earlier.

And if you want to check whether the groupname really exists, the same code does that - it will raise an error saying that "the login or group name doesn't exist".

If however you need to check whether a principal is an individual login or a group you will have to ask windows, rather than do it in sql. the cmdexec command "net group GroupName" will either respond that there's no such groupor list its members.

Tom

ArnolddG SSC-Addicted Points: 464 More actions · Answer 2

Thanks for your reply Tom,

I have been playing around a lot with what you are suggesting.

The thing is that there does not seems to be a relation between manually adding a Windows User Group and retrieving that group in SSMS.

I do know the domain and group name, so if I use this:

select * from master.sys.syslogins where name like 'Domain\GroupName' ;

...I should be able to find the group that I've just manually made, but I am not.

I am looking in SSMS under Security/Logins, but also after a refresh it does not show there.

The other way around, if I use this:

CREATE LOGIN [Domain\FoeUsers] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

... it shows in SSMS under Security/Logins, but does not show in the Windows Groups (lusmgr).

So I do not see any relation between the user group I add in SQL and the one I manually add in lusmgr.

What am I missing here?

SQL aus HH SSC Eights! Points: 833 More actions · Answer 3

lusmgr is the user/group manager of windows and had nothing (really) to do with user management in sql server (here SSMS)

First you have to create your user group in windows, then add users to that group.

After that you can add this group (both ways) via GUI or TSQL to sql server and grant access/roles to specific databases.

You CAN'T create a Windows User Group login via SSMS in Windows.

It is more a "connecting" thing, search in windows and grant specific account/group access to the sql server.

ArnolddG SSC-Addicted Points: 464 More actions · Answer 4

Hello Grashopper,

That explains it all!

I had hoped that I could check the existence this Windows user group via the TSQL script, but now I understand they are 2 separate things and it is not possible.

Thanks,

Arnold

ArnolddG SSC-Addicted Points: 464 More actions · Answer 5

I am still a bit confused.

I want to build a script to organize the access to specific tables in a database via a UDR.

I would like to use a Windows Group on the server, add users to it and allow that group the access to a SQL UDR.

I thought I could use:

ALTER ROLE [udr_db_FOEUserRole] ADD MEMBER [<<DOMAIN_NAME>>\FOEUsers]

Is that not possible at all, or am I still trying to interact between SQL and Windows Server, which is not possible?