question

UnishSQL avatar image
UnishSQL asked

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?
sql-server-2008-r2sql-serversecurityloginserverroles
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
VishalhSingh avatar image
VishalhSingh answered
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.
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

UnishSQL avatar image UnishSQL commented ·
thnk u.If both DBA group and A as Sysadmin roles how to know who is logged in A 0r DBA @vishalSingh
0 Likes 0 ·
VishalhSingh avatar image VishalhSingh commented ·
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.
0 Likes 0 ·
sp_lock avatar image
sp_lock answered
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';
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

UnishSQL avatar image UnishSQL commented ·
this was not info i am searching for but i got to know one new thing today.Thank u.....
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.