Purpose
This SP loads the PreFetch.SQLAdmins table. For more information on this table, click here.
This procedure unwinds all the groups on all the servers in a shop and then
allows you to limit the results by a specific built-in server role.
It also adds back in the accts from SQL and regular windows accts.
The goal of running this SP is to get a flat list of everyone who has admin access to SQL.
If they're coming in from a windows group then you don't know who's in those groups, or the subgroups.
Here we unwind all those groups and give a flat list of users.
Parameters
@LimitBy VARCHAR(20) = 'All',
@LimitValue varchar(20) = 'All',
@AdminType VARCHAR(20) = 'All',
@ReturnSchema BIT = 0,
@Reload BIT = 0,
@ReturnData BIT = 1,
@Help BIT = 0
@LimitBy - This is the category you want to use to limit the results. Leaving this NULL is the same as using 'All'. Values are:
- All - This collects all servers in all SLAs.
- SLA - This specifies that you want to limit the results to a specific SLA. The SLA is defined in the next param.
- App - This specifies that you want to limit the results to a specific application. The application is defined in the next param.
- Enviro - This specifies that you want to limit the results to a specific environment. The environment is defined in the next param. An environment is any entry that's in the dbo.ApplicationEnvironment table. Applications and environments need to be setup and tied to servers before you can use this option. Learn how to set up Applications and Environments.
- ID - This specifies that you want to limit the results to a specific InstanceID. InstanceID is defined in the dbo.Servers table. The specific InstanceID you want to use is defined in the following param.
- ServerName - This specifies that you want to limit the results to a specific ServerName. ServerName is defined in the dbo.Servers table. The specific ServerName you want to use is defined in the following param.
@LimitValue - This defines the specific value of the LimitBy param. Here are a few examples:
- @LimitBy = 'SLA', @LimitValue = 'GOLD'
- @LimitBy = 'ServerName', @LimitValue = 'Server1'
- @LimitBy = 'Enviro', @LimitValue = 'Production'
@AdminType - The name of the admin group in SQL.
Values:
All - Gets all built-in admin roles in SQL. These are: sysadmin, serveradmin, setupadmin, securityadmin, diskadmin, bulkadmin, dbcreator, processadmin.
sysadmin - (Default)Returns members of the sysadmin group.
serveradmin - Returns members of the serveradmin group.
setupadmin - Returns members of the setupadmin group.
securityadmin - Returns members of the securityadmin group.
diskadmin - Returns members of the diskadmin group.
bulkadmin - Returns members of the bulkadmin group.
dbcreator - Returns members of the dbcreator group.
processadmin - Returns members of the processadmin group.
*If you set this to NULL, the SP will return All.
@ReturnSchema - Whether to return the insert stmt.
This param sets the @Reload and @ReturnData params
to 0. When you use this, you want it to only return
the insert stmt. See 'Returning Schema' below for more info.
Values:
1 - Returns the insert statement and nothing else is processed.
0 - (Default)All other params are evaluated.
@Reload - This is used to specify whether you want to reload the data or not. By default it reads the prefetch data so the resultset is returned quickly. But you may want to refresh the data yourself. Be advised though that if you're in a large environment, the reload could take quite some time.
@ReturnData - If you're reloading the data do you want to simply load it into the prefetch table, or do you want to have the data returned after?
@Help - Returns a link to a support page for the SP. Hopefully it'll bring you here to this article. If you use this param it will give you the support link and exit the SP immediately. Not further processing will be attempted.
Sample Calls
Get all admin accts on all servers.
Report.SQLAdminsGet;
--This works because the defaults for @LimitBy and @LimitValue and @AdminType are 'All' Also, this does NOT reload the data.
Get all admin accts on the GOLD servers.
Report.SQLAdminsGet @LimitBy = 'SLA' @LimitValue = 'GOLD', @AdminType = 'All';
Get sysadmin accts on the GOLD servers.
Report.SQLAdminsGet @LimitBy = 'SLA' @LimitValue = 'GOLD', @AdminType = 'sysadmin';
Load PreFetch table with sysadmin data w/o returning data.
Report.SQLAdminsGet @AdminType = 'sysadmin', @Reload = 1, @ReturnData = 0;
Return pre-loaded sysadmin data.
Report.SQLAdminsGet @AdminType = 'sysadmin', @Reload = 0, @ReturnData = 1;
--This doesn't process any data; it simply queries what's already there.
Return insert stmt.
Report.SQLAdminsGet 'sysadmin', @ReturnSchema = 1;
--This doesn't process any data; it just returns the #table with the insert stmt.
Overall Process
- Delete PreFetch.ADSQLUsers by both LimitBy and LimitValue. This means that you have control over what this table holds. So if you use @LimitBy = 'SLA', @LimitValue = 'GOLD'... that's the data that will be deleted.
- Create #table and insert data by running the Report.ADAcctsInSQL SP.
- Add data from PreFetch.ADSQLUsers.
- Add data from Collector.LoginsCurrent.
- Delete logins that don't belong.
- Delete PreFetch.SQLAdmins by both LimitBy and LimitValue. This means that you have control over what this table holds. So if you use @LimitBy = 'SLA', @LimitValue = 'GOLD'... that's the data that will be deleted.
- Reload PreFetch.SQLAdmins.
- Return results if @ReturnData = 1.
Returning Schema
You may not need every column in our resultset or you may want to add this to your own process. In this case you'll need to insert your own #table with our data and we've made that very easy for you. By returning the schema you can return the #table and insert statement needed to accomplish this.
Difference between Report.SQLAdminsGet and Report.ADAcctsInSQL
These SPs are closely related but do different things.
Report.ADAcctsInSQL - Unwinds all the Windows groups and saves the flat list of users to the PreFetch.ADAcctsInSQL table. This includes both SQL admins and non-admins.
Report.SQLAdminsGet - Uses the data from Report.ADAcctsInSQL to give you a list of SQL admins.
Dependencies
Tables
PreFetch.ADAcctsInSQL
SPs
Related