This SP gets an expanded list of SQL users from AD groups.
If you have a Windows group as a login in SQL, you have no idea who's in there because
you can't see inside the AD groups. Now you can, and quite easily.
This unwinds all of the Windows groups that have logins in SQL regardless of whether they're admins or not. Therefore, this data can be used by other SPs to get all of the admins, or members of any Windows group you like.
Typically this will be run in a PreFetch job because this can take quite a long time to run if you have a lot of data. However, you can also reload the data with the params below.
*It's important to note that this SP always reloads the data. There is no option to return prefetched data. If you need to return the prefetched data, query the PreFetch.ADAcctsInSQL table directly.
This SP loads its data into the PreFetch.ADAcctsInSQL table.
Input vars:
@LimitBy
Values:
All - (Default)Returns all servers.
SLA - Returns the servers in the service level specified in @LimitValue.
Server - Returns a specific server by ServerName.
ID - Returns a specific server by InstanceID.
App - Returns servers that belong to a specific application.
Enviro - Returns servers that belong to a specific environment i.e. Dev, Prod, etc.
@LimitValue - This param is the qualifier to the @LimitBy param. The values to specify correspond to the @LimitBy param. That's to say that if you set @LimitBy = 'SLA' then this value would be one of the SLAs like 'Gold'. The same goes for @LimitBy = 'Server', you would set this to a ServerName like 'Server1'.
@ReturnData- Actually returns a resultset. Otherwise it loads
the data and returns nothing. This is so you can run this
as merely a prefetch job or to get the data itself.
Values:
1 - Returns a resultset.
0 - (Default)Reloads base data without returning a resultset.
Sample Calls:
--Load data for all servers without returning a resultset.
Report.ADAcctsInSQL; --no params needed.
--Load data for all servers and returns a resultset.
Report.ADAcctsInSQL @LimitBy = 'All', @ReturnData = 1;
--Load data for 'Gold' servers and returns a resultset.
Report.ADAcctsInSQL @LimitBy = 'All', @LimitValue = 'Gold', @ReturnData = 1;
Dependencies
Tables
PreFetch.ADAcctsInSQL
SPs
Collector.LoginsCurrent
Related