Who Can Get The DBA Fired Report

So there you are, sitting at your desk, idly clicking in SSMS, admiring your little kingdom of SQL Servers. Everything looks good. Jobs are running correctly. No performance problems to be seen. Everything is humming along smoothly. It’s tempting to relax a little and perhaps take some time to peruse a few blogs or check out some lolcat pictures.

If you’ve ever seen a horror movie, you know it’s times like these when a guy wearing a hockey mask appears behind your chair and thrusts a three foot long machete through your chair and your chest. This is why DBAs are paranoid. (Well, one of the reasons.)

This DBA never saw it coming

Just when you think everything is running smoothly, your phone will ring and someone will tell you they accidentally dropped a database. Or shutdown a server. Or committed some other catastrophic action you thought it would be impossible for a user to do. Never underestimate users. They are a wily bunch and quite possibly the root of all evil.

As Guardian Of The Data, a DBA’s job is to allow users to access the data they need but stop them from destroying that data or making it inaccessible to others. In short, your job is to stop them from hurting themselves and others. Because if one of them does do something horrific like dropping the database that contained all the orders your company filled for the last 3 years, guess who is going to get blamed? That’s right – DBA Guy.

So it’s up to you to protect the data. You do that with access permissions. Users should only have the permissions they need in the database server to perform their jobs. Nothing more.

Unfortunately, many times users may attempt to do something that they don’t have permissions to do. They might not know what permissions they need, so they ask for full admin access, because everyone knows that having full access makes all permission issues go away. Sometimes a DBA will give it to them, perhaps with the intention of revoking the permission as soon as the user’s task is completed. But even DBAs get busy and forget and you can end up with lots of logins that have admin rights on your servers. Or your servers use Windows groups for access and the network admins are rather non-discriminatory about who they add to the SQL Admins group. One way or another, there are likely way more people who have full access to your SQL Servers than you think there are.

You need to get a handle on this. Remember how I said it was DBA Guy that will get the blame if something goes wrong? The worse the disaster, the more true that is. So you need what Brent Ozar has called a “People Who Can Get The DBA Fired” report. It is a list of all the accounts with admin rights on your SQL Servers. Ideally, it should be created via an automated process. As I mentioned, network admins can add people to Windows groups at any time. If your SQL Admins group looks good today, there’s no guarantee that Bobby Tables from Marketing won’t find a way to talk himself into getting access to that group tomorrow.

I created a little procedure that will gather a list of every login that has admin rights on your SQL Servers. It takes a list of servers from a table called ServersToCheckDiskStats, creates a linked server entry to that server, then gathers data about the logins with admin rights and deletes the linked server entry. (Yes, I know it’s not really appropriately named, but I already had this list set up from my drive space monitoring routine, so I used it here as well. See that post for table definitions and details of how it works.)

As I have explained before, I like to have single script for a task that will work against any version of SQL Server that we have in use. Handling this means my scripts are bigger, take more time to develop, and may be a bit more complicated. But the payoff is that I have a single script that can run against any server in my environment and won’t break if a particular server gets upgraded to a newer SQL version. The script below can be run against SQL 2000, 2005, 2008, and 2008 R2 servers and will use the appropriate system tables for each version. (The server version is specified in the ServersToCheckDiskStats table and, for simplicity, it assumes 2008 R2 is the same as 2008.

The routine requires a SQL login and password to create the linked server connections. This login should be created on each server you wish to monitor. The routine uses two tables – SQLAdminAccess and ADAdminGroupMembers. DDL code for the tables is below. The first table will store the logins that have admin access to a particular server. The second contains members of any groups (Windows or local machine) that have admin access. (This table is populated by a PowerShell script I will discuss later.)

CREATE TABLE [dbo].[SQLAdminAccess]
       (
        [ServerName] [varchar](128) NOT NULL
       ,[LoginName] [nvarchar](128) NOT NULL
       ,[AccessLevel] [varchar](15) NOT NULL
       ,[Type] [char](1) NOT NULL
       ,[TypeDesc] [nvarchar](60) NOT NULL
       ,[IsDisabled] [int] NOT NULL
       ,[State] [char](1) NULL
       ,[Created] [varchar](10) NULL
       ,[Updated] [varchar](10) NULL
       ,[StatDate] [date] NULL
       ,CONSTRAINT [PK_SQLAdminAccess] PRIMARY KEY CLUSTERED
            ([ServerName] ASC, [LoginName] ASC, [AccessLevel] ASC)
            WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
                  IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
                  ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
       )
ON     [PRIMARY]

GO

ALTER TABLE [dbo].[SQLAdminAccess] ADD  CONSTRAINT [DF_SQLAdminAccess_StatDate]  DEFAULT (GETDATE()) FOR [StatDate]
GO

CREATE TABLE [dbo].[ADAdminGroupMembers]
       (
        [ServerName] [nvarchar](128) NULL
       ,[ADGroupName] [nvarchar](128) NOT NULL
       ,[ADAccountName] [nvarchar](128) NOT NULL
       ,[StatDate] [date] NULL
       )
ON     [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_ADAdminGroups] ON [dbo].[ADAdminGroupMembers]
	(
	[ADGroupName] ASC,
	[ADAccountName] ASC
	)
	WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
	SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
	DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
	ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ADAdminGroupMembers] ADD  CONSTRAINT [DF_ADAdminGroupMembers_StatDate]  DEFAULT (GETDATE()) FOR [StatDate]
GO

Now that the tables are defined, let’s look at the procedure that does all the work:

CREATE PROCEDURE [dbo].[GatherSQLAdminAccessData]
AS
-- =============================================
-- Author: Shaun Stuart, shaunjstuart.com
-- Create date: May 22, 2012
-- Description: Procedure to collect info about SQL account with sysadmin rights
-- =============================================
--

      DECLARE @ServerName VARCHAR(128)
      DECLARE @ServerDisplayName VARCHAR(128)
      DECLARE @ServerNamePK INT
      DECLARE @PersistLink BIT
      DECLARE @SQLCmd VARCHAR(3000)
      DECLARE @LinkedServerLogin VARCHAR(50)
      DECLARE @LinkedServerLoginpwd VARCHAR(50)
      DECLARE @SQLServerVersion CHAR(4)
      DECLARE @LoginName NVARCHAR(128)

      SET @LinkedServerLogin = '<enter your login account here>'
      SET @LinkedServerLoginpwd = '<enter your password here>'

/* Get a list of the servers to gather data from and their SQL version */

      DECLARE ServersCursor CURSOR
      FOR
              SELECT    ServerName
                       ,ServerDisplayName
                       ,PK
                       ,PersistLink
                       ,SQLServerVersion
              FROM      ServersToCheckDiskStats
              WHERE     IncludeInCheck = 1

/* Delete old data first */

      DELETE    FROM dbo.SQLAdminAccess
      DELETE    FROM dbo.ADAdminGroupMembers
      OPEN    ServersCursor
      FETCH NEXT FROM ServersCursor INTO @ServerName, @ServerDisplayName,
            @ServerNamePK, @PersistLink, @SQLServerVersion

      WHILE @@FETCH_STATUS = 0
            BEGIN

/* create linked server to get info. Check PersistLink value to see if linked server should remain or be deleted */

                  IF EXISTS ( SELECT    *
                              FROM      master.sys.servers
                              WHERE     @ServerName = name )
                     AND @PersistLink = 0
                     AND @@SERVERNAME <> @ServerName
                     BEGIN
                           EXEC sp_dropserver
                            @ServerName
                           ,droplogins
                     END

                  IF @PersistLink = 0
                     AND @@SERVERNAME <> @ServerName   /*if link is persisted, linked server is already present, no need to add */
                     BEGIN
                           EXEC sp_addlinkedserver
                            @server = @ServerName
                     END

                  EXEC sp_addlinkedsrvlogin
                    @ServerName
                   ,'false'
                   ,NULL
                   ,@LinkedServerLogin
                   ,@LinkedServerLoginPwd

/* pull all SQL logins that have sysadmin rights using the SQL version-appropriate system table */

                  SELECT	@SQLCmd = CASE @SQLServerVersion
                    WHEN '2008' THEN
                    'INSERT  INTO dbo.SQLAdminAccess
						(ServerName
						,LoginName
						,AccessLevel
						,Type
						,TypeDesc
						,IsDisabled
						,State
						,Created
						,Updated)
					SELECT  ' + '''' + @ServerDisplayName + '''' + '
						,sp.name AS [loginname]
						,sp2.name AS AccessLevel
						,sp.type
						,[type_desc] = CASE sp.type
							WHEN ''U'' THEN ''WINDOWS_LOGIN''
							WHEN ''S'' THEN ''SQL_LOGIN''
							WHEN ''G'' THEN ''WINDOWS_GROUP''
						END
						,sp.is_disabled
						,sper.state
						,CONVERT(VARCHAR(10), sp.create_date, 101) AS [created]
						,CONVERT(VARCHAR(10), sp.modify_date, 101) AS [update]
					FROM    [' + @ServerName + '].master.sys.server_principals AS sp
					JOIN [' + @ServerName + '].master.sys.server_role_members AS srm ON sp.principal_id = srm.member_principal_id
					JOIN [' + @ServerName + '].master.sys.server_principals AS sp2 ON srm.role_principal_id = sp2.principal_id
					JOIN [' + @ServerName + '].master.sys.server_permissions AS sper ON sper.grantee_principal_id = sp.principal_id
					WHERE sper.TYPE = ''cosq''
                         AND sp2.name IN (''sysadmin'',
										  ''serveradmin'',
										  ''securityadmin'')'
					WHEN '2005'	THEN /* same as 2008 */
					'INSERT  INTO dbo.SQLAdminAccess
						(ServerName
						,LoginName
						,AccessLevel
						,Type
						,TypeDesc
						,IsDisabled
						,State
						,Created
						,Updated)
					SELECT  ' + '''' + @ServerDisplayName + '''' + '
						,sp.name AS [loginname]
						,sp2.name AS AccessLevel
						,sp.type
						,[type_desc] = CASE sp.type
							WHEN ''U'' THEN ''WINDOWS_LOGIN''
							WHEN ''S'' THEN ''SQL_LOGIN''
							WHEN ''G'' THEN ''WINDOWS_GROUP''
						END
						,sp.is_disabled
						,sper.state
						,CONVERT(VARCHAR(10), sp.create_date, 101) AS [created]
						,CONVERT(VARCHAR(10), sp.modify_date, 101) AS [update]
					FROM    [' + @ServerName + '].master.sys.server_principals AS sp
					JOIN [' + @ServerName + '].master.sys.server_role_members AS srm ON sp.principal_id = srm.member_principal_id
					JOIN [' + @ServerName + '].master.sys.server_principals AS sp2 ON srm.role_principal_id = sp2.principal_id
                                             AND sp2.name IN (''sysadmin'',
                                                              ''serveradmin'',
                                                              ''securityadmin'')
					JOIN [' + @ServerName + '].master.sys.server_permissions AS sper ON sper.grantee_principal_id = sp.principal_id
					WHERE sper.TYPE = ''cosq''
						     AND sp2.name IN (''sysadmin'',
									''serveradmin'',
									''securityadmin'')'
					WHEN '2000' THEN
                    'INSERT  INTO dbo.SQLAdminAccess
						(ServerName
						,LoginName
						,AccessLevel
						,Type
						,TypeDesc
						,IsDisabled
						,State
						,Created
						,Updated)
					SELECT  ' + '''' + @ServerDisplayName + '''' + '
						,loginname
						,''sysadmin'' AS AccessLevel	/* Cheating here. Only looking for this access level */
						,''S'' AS type					/* not serveradmin and securityadmin for 2000 servers */
						,''SQL_LOGIN'' AS type_desc
						,CASE hasaccess
							WHEN 1 THEN 0
							ELSE 1
						END AS is_disabled
						,''G'' AS State /* SQL logins cant be denied acess in sql 2000 */
						,CONVERT(VARCHAR(10), createdate, 101) AS [created]
						,CONVERT(VARCHAR(10), updatedate, 101) AS [update]
					FROM    [' + @ServerName + '].master.dbo.syslogins
					WHERE   sysadmin = 1
							AND syslogins.isntname = 0
							AND syslogins.isntgroup = 0
							AND syslogins.isntuser = 0
					UNION ALL
					SELECT  ' + '''' + @ServerDisplayName + '''' + '
							,loginname
							,''sysadmin'' AS AccessLevel	/* Cheating here. Only looking for this access level */
							,''U'' AS type					/* not serveradmin and securityadmin for 2000 servers */
							,''WINDOWS_LOGIN'' AS type_desc
							,CASE hasaccess
								WHEN 1 THEN 0
								ELSE 1
							END AS is_disabled
							,CASE denylogin
								WHEN 1 THEN ''D''
								ELSE ''G''
							END AS State
							,CONVERT(VARCHAR(10), createdate, 101) AS [created]
							,CONVERT(VARCHAR(10), updatedate, 101) AS [update]
					FROM [' + @ServerName + '].master.dbo.syslogins
					WHERE   sysadmin = 1
							AND syslogins.isntname = 1
							AND syslogins.isntgroup = 0
							AND syslogins.isntuser = 1
					UNION ALL
					SELECT  ' + '''' + @ServerDisplayName + '''' + '
							,loginname
							,''sysadmin'' AS AccessLevel	/* Cheating here. Only looking for this access level */
							,''G'' AS type					/* not serveradmin and securityadmin for 2000 servers */
							,''WINDOWS_GROUP'' AS type_desc
							,CASE hasaccess
								WHEN 1 THEN 0
								ELSE 1
							END AS is_disabled
							,CASE denylogin
								WHEN 1 THEN ''D''
								ELSE ''G''
							END AS State
							,CONVERT(VARCHAR(10), createdate, 101) AS [created]
							,CONVERT(VARCHAR(10), updatedate, 101) AS [update]
					FROM [' + @ServerName + '].master.dbo.syslogins
					WHERE   sysadmin = 1
						AND syslogins.isntname = 1
						AND	syslogins.isntgroup = 1
						AND syslogins.isntuser = 0'
					ELSE        /* not a SQL version this procedure handles */
                         'INSERT    DatabaseDiskStats
                        (ServersToCheckPK,
                         Server_name,
                         Database_name,
                         [Filename],
                         Drive_letter)
						SELECT  ' + CONVERT(VARCHAR(6), @ServerNamePK) + ',' + ''''
                                             + @ServerDisplayName + '''' + ','
                                             + ''''
                                             + 'undefined SQL version in table ServersToCheckDiskStats'
                                             + '''' + ' AS LoginName ,' + ''''
                                             + 'ERROR' + '''' + ',' + ''''
                                             + '*' + ''''
					END
 PRINT @sqlcmd /* for debugging use */
					EXEC (@SQLCmd)

/* Done. Drop linked server if required */

                  IF @persistLink = 0
                     AND @@SERVERNAME <> @ServerName
                     BEGIN
                           EXEC sp_dropserver
                            @ServerName
                           ,droplogins
                     END

                  FETCH NEXT FROM ServersCursor INTO @ServerName,
                        @ServerDisplayName, @ServerNamePK, @PersistLink,
                        @SQLServerVersion

            END

      CLOSE ServersCursor
      DEALLOCATE ServersCursor

Let me say a few words about this. You’ll notice the code for 2008 and 2005 servers checks not only for members of the sysadmin group, but also for members of the serveradmin and securityadmin groups. This is because members of those groups can also wreck havoc on your servers. In regards to the securityadmin group, Microsoft even states “The securityadmin group shold be treated as equivalent to the sysadmin group.” The same caveats apply to SQL 2000 servers, but due to the way the system tables are setup in that version, I only check for members of the sysadmin group on those servers. In my situation, we have few SQL 2000 servers and those we do have will be going away soon, so this isn’t a big concern for me. If you want to modify this routine to also check the serveradmin and securityadmin group on 2000 servers, you’ll need to modify the code to add a bunch more UNION statements and check if the syslogins.serveradmin and syslogins.securityadmin fields are equal to 1.

The code also identifies accounts that have been disabled and / or denied access. I made this distinction because in my environment, I came across some Windows logins that had sysadmin rights, but the accounts had been removed from Active Directory. If this is the case, SQL cannot deactivate the logins (it checks AD and returns an error that the account does not exist), but you can deny them access. Why do I care? Because before I delete an account, I want to be 100% sure it is not being used. To do that, I disable or deny access to the account and let it sit for a period of time before deleting it. If any processes fail, I know the account was in use and I can easily re-enable it before determining the correct access level it needs. Now, I know if a Windows login doesn’t exist in Active Directory, it’s a fairly safe bet that it’s no longer being used to connect to SQL Server. But DBAs are paranoid, remember?

You’ll notice the tables have a column that defaults to the current date. This is so my report can display the date this information was gathered.

The final piece of the puzzle is to determine the members of any Windows or local server groups that have admin rights. In order to do that, you could use the xp_loginfo stored procedure. I opted not to go this route. Actually, I tried it initially, but was having difficulties running this against linked servers. Instead, I decided to use PowerShell to obtain this information, which gave me a chance to learn a bit more about PowerShell. I wrote two scripts – one to get members of Active Directory groups and one to get members of local machine groups. (In particular, SQL 2005 creates local machine groups that may have admin rights.) Again, I’ve modified these scripts from my previous Drive Space Monitoring routine, so the variable names might be a bit misleading.

Save this script as SQLAdminGroupMembers.ps1.

# ---------------------------------------------------------------------------
### Author:
### Shaun Stuart
### www.shaunjstuart.com
# ---------------------------------------------------------------------------

$DiskStatsServer = '<enter your server name here>'
$DiskStatsDB = '<enter the database name here>'
$SelectQuery = @"
                SELECT  DISTINCT RIGHT(LoginName, (LEN(loginname) - CHARINDEX('\', loginname))) AS LoginName
                         ,LEFT(loginname, (CHARINDEX('\', loginname) - 1)) AS DomainName
                FROM    [DSFCUReportingDB].[dbo].[SQLAdminAccess]
                WHERE   DSFCUReportingDB.dbo.SQLAdminAccess.LoginName LIKE '\%'
                        AND TypeDesc = 'WINDOWS_GROUP'
"@

#######################
## MAIN             ##
#######################

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$DiskStatsServer;Database=$DiskStatsDB;Integrated Security=True"

# Can't issue the update command via the same connection as the SELECT because of the DataReader loop, so we need a new connection

$SqlUpdConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlUpdConnection.ConnectionString = "Server=$DiskStatsServer;Database=$DiskStatsDB;Integrated Security=True"

$SqlConnection.open()
$SQLUpdConnection.open()

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SelectQuery
$SqlCmd.Connection = $SqlConnection

$SqlUpdCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlUpdCmd.Connection = $SqlUpdConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$ds = $SqlCmd.ExecuteReader()
while ($ds.Read()) {
	$LoginName = $ds.GetValue(0)
    echo "LoginName $LoginName"
    $a = '' # Clear out the array. Otherwise, groups that don't exist will return members of previous group
    $a = get-adgroupmember $LoginName
    foreach ($objitem in $a) {
        #'The Windows account {0} is in group {1}' -f $objitem.samaccountname, $LoginName

        	$UpdateQuery = @"
                	INSERT ADAdminGroupMembers
                        (ServerName
                         ,ADGroupName
                         ,ADAccountName
                         ,StatDate)
                	SELECT ''
                        ,'$LoginName'
                	    ,'$($objitem.SamAccountName)'
                        , '$(Get-Date)'
"@
            #echo $UpdateQuery
	$SqlUpdCmd.CommandText = $UpdateQuery
	$SqlUpdCmd.ExecuteNonQuery()

        }

}
$ds.Close()
$SqlConnection.Close()
$SqlUpdConnection.Close()

Save this script as LocalSQLAdminGroupMembers.ps1.

# ---------------------------------------------------------------------------
### Author:
### Shaun Stuart
### www.shaunjstuart.com
# ---------------------------------------------------------------------------

$DiskStatsServer = '<enter your server name here>'
$DiskStatsDB = '<enter your database name here>'
$SelectQuery = @"
            SELECT DISTINCT
                    CASE WHEN Servername LIKE '%\%' /* remove instance name */
                         THEN LEFT(LEFT(DSFCUReportingDB.dbo.SQLAdminAccess.ServerName,
                                        REPLACE(CHARINDEX(',', ServerName) - 1, -1, 129)),
                                   CHARINDEX('\', servername)-1)
                         ELSE LEFT(DSFCUReportingDB.dbo.SQLAdminAccess.ServerName,
                                   REPLACE(CHARINDEX(',', ServerName) - 1, -1, 129))
                    END AS ServerName
                   ,RIGHT(LoginName, (LEN(loginname) - CHARINDEX('\', loginname))) AS LoginName
            FROM    [DSFCUReportingDB].[dbo].[SQLAdminAccess]
            WHERE   DSFCUReportingDB.dbo.SQLAdminAccess.TypeDesc = 'windows_group'
                    AND DSFCUReportingDB.dbo.SQLAdminAccess.LoginName NOT LIKE '%'
                    AND DSFCUReportingDB.dbo.SQLAdminAccess.LoginName NOT LIKE 'NT Ser%'

"@

# Function Get-LocalGroupMembers from http://gallery.technet.microsoft.com/scriptcenter/List-local-group-members-762b48c5

function Get-LocalGroupMembers
{
    param(
        [parameter(Mandatory=$true,ValueFromPipeline=$true,ValueFromPipelineByPropertyName=$true)]
        [Alias("Name")]
        [string]$ComputerName,
        [string]$GroupName = "Administrators"
    )

    begin {}

    process
    {
        # If the account name of the computer object was passed in, it will
        # end with a $. Get rid of it so it doesn't screw up the WMI query.
        $ComputerName = $ComputerName.Replace("`$", '')

        # Initialize an array to hold the results of our query.
        $arr = @()

        $wmi = Get-WmiObject `
                        -ComputerName $ComputerName  `
                        -Query "select * from win32_groupuser where GroupComponent=`"Win32_Group.Domain='$ComputerName'`,Name='$GroupName'`""
        # Parse out the username from each result and append it to the array.
        if ($wmi -ne $null)
        {
            foreach ($item in $wmi)
            {
                $arr += ($item.PartComponent.Substring($item.PartComponent.IndexOf(',') + 1).Replace('Name=', '').Replace("`"", ''))
            }
        }

        $hash = @{ComputerName=$ComputerName;Members=$arr}
        return $hash
    }

    end{}
}

#######################
## MAIN             ##
#######################

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$DiskStatsServer;Database=$DiskStatsDB;Integrated Security=True"

# Can't issue the update command via the same connection as the SELECT because of the DataReader loop, so we need a new connection
# (This may not be true for SQL 2005+. I think SQL 2005+ uses MARS, which allows this.)

$SqlUpdConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlUpdConnection.ConnectionString = "Server=$DiskStatsServer;Database=$DiskStatsDB;Integrated Security=True"

$SqlConnection.open()
$SQLUpdConnection.open()

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SelectQuery
$SqlCmd.Connection = $SqlConnection

$SqlUpdCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlUpdCmd.Connection = $SqlUpdConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$ds = $SqlCmd.ExecuteReader()
while ($ds.Read()) {
	$ServerName = $ds.GetValue(0)
    $LoginName = $ds.GetValue(1)
    echo "ServerName $ServerName ---- LoginName $LoginName"
    $a = '' # Clear out the array. Otherwise, groups that don't exist will return members of previous group
    $a = (Get-LocalGroupMembers -ComputerName $ServerName -GroupName $LoginName).Members
    echo $a

    foreach ($objitem in $a) {

        	$UpdateQuery = @"
                	INSERT ADAdminGroupMembers
                        (ServerName
                         ,ADGroupName
                         ,ADAccountName
                         ,StatDate)
                	SELECT '$ServerName'
                        ,'$LoginName'
                        ,'$objitem'
                        ,'$(Get-Date)'
"@
#            echo $UpdateQuery
	$SqlUpdCmd.CommandText = $UpdateQuery
	$SqlUpdCmd.ExecuteNonQuery()

        }

}
$ds.Close()
$SqlConnection.Close()
$SqlUpdConnection.Close()

To pull everything together, create a SQL job that runs as often as you want to collect this information. Step 1 in the job is to execute the stored procedure. Step 2 is to execute the first PowerShell script. Step 3 is to execute the second PowerShell script. I will note I have my job run the PowerShell script via CmdExec (powershell.exe &'<full path to script>\SQLAdminGroupMembers.ps1 – the ampersand is needed). I was not able to get the scripts to run using the PowerShell Job Step Type.

Permissions Needed

The SQL login you use to create your linked servers will need to exist on each server you want to monitor. The login needs to be in the public SQL group and needs the VIEW ANY DEFINITION right. For SQL 2000 servers, it needs access to the master database as a member of the public database role.

In order for the PowerShell scripts to gather group members, the account executing the script (which will be the account SQL Server Agent is using if you run this as a scheduled job), needs local admin rights on each machine it connects to (in order to get local group members). (See below for a possible way around this if you don’t want this security risk.)

Additionally, the Active Directory PowerShell module needs to be loaded on the server that executes the scripts. (It is installed by default on Windows Server 2008 and higher).

What if you have machines in multiple domains? As written, these PowerShell scripts assume a single domain. However, you can modify them to pass credentials to another domain and execute as a different user in that domain. In order to do this, the other domain needs to have at least one domain controller running Windows Server 2008 R2. Then, you can use the New-PSDrive cmdlet. Details can be found here and here. You can also use this method to supply alternate credentials for your current domain if you don’t want to give local admin rights on your servers to the SQL Server Agent service account. Unfortunately, I do not have an environment where I can test this, so this exercise is left for the reader.

There you have it. An easy, automate-able method to get a list of people who can get you fired! It’s a simple matter to create a report in SSRS that pulls data from your two tables. (A final code piece is shown below. This is the data set definition for my SSRS report.) When I first ran this in my environment, I ended up with a report that was 22 pages long! I’m working on paring that down now. Check out your systems. I think you might also be surprised.

SELECT  [ServerName]
       ,[LoginName]
       ,[AccessLevel]
       ,[TypeDesc]
       ,[Created]
       ,[Updated]
       ,[StatDate]
       ,CASE WHEN dbo.SQLAdminAccess.IsDisabled = 1
                  AND STATE = 'D' THEN 'Disabled and denied access'
             WHEN dbo.SQLAdminAccess.IsDisabled = 0
                  AND STATE = 'D' THEN 'Denied access'
             WHEN dbo.SQLAdminAccess.IsDisabled = 1
                  AND STATE <> 'D' THEN 'Disabled'
             ELSE ''
        END AS [Status]
FROM    [SQLAdminAccess]
ORDER BY ServerName
       ,LoginName

Share

Leave a Reply

Your email address will not be published. Required fields are marked *

I am a real person and can prove it by doing this math problem: (required) Time limit is exhausted. Please reload CAPTCHA.