Windows Group as Login linked to User Login

Question

Windows Group as Login linked to User Login

sudhirnune

SSCrazy

Points: 2342
More actions
February 21, 2012 at 10:50 pm

#268681

This is how the security is designed in the Sql server.
> Windows Group is created and added it as part of Login (SO that any user part of the Group can login to the serve and get all the privlages given to teh Windows Group)
my challenge is : I need to find out the Windows Group to which the User Who Logged in belong to.
Please need your help on this.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

sudhirnune (2/21/2012)
my challenge is : I need to find out the Windows Group to which the User Who Logged in belong to.

All groups they belong to or the specific one that allowed them access to the SQL Server instance?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

sudhirnune SSCrazy Points: 2342 More actions · Answer 2

I was not able to get your Question ... Can you elaborate

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

sudhirnune (2/21/2012)
This is how the security is designed in the Sql server.
> Windows Group is created and added it as part of Login (SO that any user part of the Group can login to the serve and get all the privlages given to teh Windows Group)
my challenge is : I need to find out the Windows Group to which the User Who Logged in belong to.
Please need your help on this.

You can't, not directly from SQL Server. At least not anyway that I know.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

sudhirnune SSCrazy Points: 2342 More actions · Answer 4

Thanks for the Reply,

Wanted to check is there any way to check to which all proxies a system_user has access to

any function / query which can show me that info.

Lowell SSC Guru Points: 323518 More actions · Answer 5

these two extended procs examples might help.

one tells you who belongs to a group, the other can tell you all the grouls i belong to:

EXEC master..xp_logininfo

@acctname = 'mydomain\lowell',

@option = 'all' -- Show all paths a user gets his auth from

go

EXEC master..xp_logininfo

@acctname = 'mydomain\authenticatedusers',

@option = 'members' -- show group members

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

Lowell (2/22/2012)
these two extended procs examples might help.
one tells you who belongs to a group, the other can tell you all the grouls i belong to:
EXEC master..xp_logininfo
@acctname = 'mydomain\lowell',
@option = 'all' -- Show all paths a user gets his auth from
go
EXEC master..xp_logininfo
@acctname = 'mydomain\authenticatedusers',
@option = 'members' -- show group members

Handy Lowell, very handy. Thank you.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

sudhirnune SSCrazy Points: 2342 More actions · Answer 7

thanks a lot for the information "Lowell".

Its solved my issue.

When checked the Store Procedure, we were not able execute the OPENROWSET which get the network user details. which can not be executed as a sql select statement.

Lowell SSC Guru Points: 323518 More actions · Answer 8

glad I could help a little bit!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

Thanks Lowell, as I suspected I'm a member of the BUILTIN\Administrators Group.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/