how to find which windows principal has logged in sql server

I Have Windows User Login and Windows User Group.

Consider User as A and Group as DBA group.

In scenario MR. A is part of DBA group. A as only basic rights in sql server And DBA group as SYSADMIN rights

I know if MR.A connects to sql server he may connect with DBA has he was part of it as SYSadmin Rights.

What if Both A and DBA group as SYSAdmin rights.Now how can know by which means he was Connected/logged in sql server.

by DBA group/or as A?

more ▼

asked Oct 29, 2014 at 07:49 AM in Default

avatar image

419 6 11 21

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

If 'A' is a member of DBAGroup and this group is having SYSADMIN permission then whenever 'A'will login he will get SA level access only. He can't login separately as only 'A' and as member of DBAGroup.

this is due to the behavior that authentication process overwrites the user's permission with the group access level.

Hope this helps.

more ▼

answered Oct 29, 2014 at 09:13 AM

avatar image

851 3 6

thnk u.If both DBA group and A as Sysadmin roles how to know who is logged in A 0r DBA @vishalSingh

Oct 29, 2014 at 09:17 AM UnishSQL

If DBAGroup in this case is a windows distribution group you will NT_UserName and LoginName..go through them and they are self explanatory.

Hope this helps.

Oct 29, 2014 at 09:45 AM VishalhSingh
(comments are locked)
10|1200 characters needed characters left

If I understand you correctly then you can use the query below to find the authentication path

 EXEC sys.xp_logininfo @acctname = N'MyDomain\Username',  @option = 'all';
more ▼

answered Oct 29, 2014 at 08:02 AM

avatar image

10.9k 27 37 37

this was not info i am searching for but i got to know one new thing today.Thank u.....

Oct 29, 2014 at 08:37 AM UnishSQL
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 29, 2014 at 07:49 AM

Seen: 261 times

Last Updated: Oct 31, 2014 at 01:25 PM

Copyright 2018 Redgate Software. Privacy Policy