logging into an instance with SSMS (granting/viewing access to SSMS)

Question

I am curious if there is another location outside of the logins within each instance of SQL on where a userID could be given instance access access and being able to get an SSMS object view of the SQL instance? I have a user who is able to login to an instance with SSMS and her/his ID is not a member of any of the defined groups or one of the users on the list of logins within the instance of SQL. As you can see in the recently added screenshot below there is an ID that is a member of TBHC domain that is not found in the list of users on the left panel. the groups that I left unhighlighted does not have that user as a member of that group. yet the user is able to login with windows authentication with that TBHC domain ID.

Answer 1

Hello,

I don't see the screenshot you are referring to (I get a red X that the picture isn't found). What you could do is impersonate the login and check sys.login_token and sys.server_principals to see what windows groups or roles the login could be assocated with.

Example:

EXECUTE AS LOGIN='MyDomain\MyLogin'

select distinct sp.name, sp.principal_id, lt.principal_id from sys.server_principals sp inner join sys.login_token lt on sp.principal_id = lt.principal_id
order by sp.name ASC


REVERT

-Sean

Answer 2

issue resolved. was a mental mistake. 2 node cluster, group membership for builtin\administrators that users were running SSMS from did not exist, but in the active-passive cluster the instance was currently running on the 2nd node and missed that they were in administrators on that node. we all get a blonde day excuse now and again right? thanks for your response though, Sean!

Answer 3

No problem, I figured this was the case but didn't want to jump to conclusions. I see this question all the time, especially with SQL Server 2005 where the BUILTIN groups were automatically included (where as in 2008 they are not!).

-Sean