Last week we had a problem and it went on further than it should have because I did not have access to the local machine.
Active Directory & Security Account Management (SAM)
There are two security databases that matter at the OS Level.
On each local machine, we have Security Account Management ( SAM ).
And, on the enterprise level we have Active Directory.
Thankfully, we can query both using xp_loginfo.
declare @privilege varchar(10) set @privilege = 'Not wanted' EXEC xp_logininfo @acctname = [group-name]' , @option = 'members' , @privilege = @privilege output
declare @privilege varchar(10) set @privilege = 'Not wanted' EXEC xp_logininfo @acctname = 'BUILTIN\Administrators' , @option = 'members' , @privilege = @privilege output
- As we would expect when we query the local BUILTIN\Administrators group
- We see that the AD Domain Admins groups are listed
- Also listed is an additional internal group that I can’t disclose
- And, one other local account user; that I can not talk about either
It is a simple query, but it allows me to write a script that queries all of SQL Server Instances.
Upon querying those servers, we can arrange for our internal DBA group to have remote desktop access to the machines.
- Please extend xp_logininfo to be be able to query groups that do not have SQL Server Access
ID :- 3112040
Status :- Active
Type :- Suggestion ( Bug )
Opened On :- 2016-Nov-18th