SQL Lessons: PowerShell basics

Part of my series pointing to valuable instruction on the web.

This one demonstrates the following functions by Chad Miller(Blog|Twitter)

  • invoke-sqlcmd2
  • Write-DataTable
  • Out-DataTable
  • Add-SqlTable

Use PowerShell to Collect Server Data and Write to SQL

Learn about Windows PowerShell

Source: blogs.technet.microsoft.com/heyscriptingguy/2010/11/01/use-powershell-to-collect-server-data-and-write-to-sql/

SQL Lessons: SQL Server Configuration Manger

Part of my series pointing to valuable instruction on the web.

What Every Accidental DBA Needs to Know Now: SQL Server Configuration Manger

The SQL Server Configuration Manager is an critical but often overlooked application for proper SQL Server instance management and, well, “configuration”. In Tim Ford’s continuing series aimed at the Accidental Database Administrator he takes time to explain the basics of this tool, its use, and its features.

Source: m.sqlmag.com/database-security/what-every-accidental-dba-needs-know-now-sql-server-configuration-manger

SQL: Test SQLAgentReaderRole role in msdb to allow viewing jobs

Sometimes a developer or analyst needs to see SQL Agent jobs or its history.

Create a login, add to role, open SSMS and log in as that login which has no other permissions, and view jobs, check history, and notice everything is grayed out.

CREATE LOGIN jperryx WITH PASSWORD = '1anGxxp22'

USE [msdb]
CREATE USER [jperryx] FOR LOGIN [jperryx] 
ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [jperryx]

After testing yourself, and knowing what they can do with this access, you can decide if you should grant it or not.

You can find more about it here.

Now clean up.

DROP USER jperryx
DROP LOGIN jperryx

SQL: Find database users who don’t have a login

Find users who don’t have a login. These are real orphans. We find by name because they might be out of sync by id. We assume that’s already been checked.
One caution is some users may have login access via an Active Directory group. Do not run the DROP statements without testing the impact.

SELECT  [sp].[name] AS [LoginName],
        [dp].[name] AS [UserName],
   --     'DROP SCHEMA [' + QUOTENAME([dp].[name]) + '];' + CHAR(10) +
        'DROP USER ' + QUOTENAME([dp].[name]) + ';'
FROM    [sys].[database_principals] [dp]
LEFT OUTER JOIN [sys].[server_principals] [sp]
ON      [sp].[sid] = [dp].[sid]							-- sid could be incorrectly mismatched 
        AND CHARINDEX([dp].[name], [sp].[name]) > 0		-- here we are trying to match by login NAME which makes sense. Occasionally users are created without domain so catch those too.
WHERE   [dp].[type] IN ('U', 'G')
        AND [dp].[principal_id] <> 1
        AND [sp].[name] IS NULL
ORDER BY [UserName]

SQL: Audit to find identical schedules on multiple jobs

At one point it surprised me to see a SQL Agent job schedule in more than one job. A lot of things can cause this, especially if you script and clone jobs.

Here’s the audit you can use to find the jobs which share the exact same schedule. If you change it in one place, ooops, it changes everywhere!

Audit to find identical schedules on multiple jobs

SELECT  [j].[name] [JobName],
        [s].[name] [DupScheduleName],
FROM    [msdb].[dbo].[sysjobschedules] [sjs]
JOIN    [msdb].[dbo].[sysschedules] [s]
ON      [s].[schedule_id] = [sjs].[schedule_id]
JOIN    [msdb].[dbo].[sysjobs] [j]
ON      [j].[job_id] = [sjs].[job_id]
WHERE   [sjs].[schedule_id] IN (SELECT   [sjs].[schedule_id]
                               FROM     [msdb].[dbo].[sysjobschedules] [sjs]
                               JOIN     [msdb].[dbo].[sysschedules] [s]
                               ON       [s].[schedule_id] = [sjs].[schedule_id]
                               GROUP BY [sjs].[schedule_id]
                               HAVING   COUNT(*) > 1)

After you find them, you will have to drop the dups and recreate using a different name. Possibly just recreating and getting a new schedule_id would work as well.

SQL: Start in single user mode and run commands.

Change the SQL server service for SQL Server 2012 2014 to this
adding -m to the startup parameters.

Before you do that you really want to

  • Get everyone off the server.
  • Disable SQL Agent service changing to manual
  • Close any SSMS object explorer windows you or other administrators have open.
    • Remember it’s single user mode.
  • Stop the services, for SQL and any others which may automatically reconnect once SQL is back up.
  • Possibly leave SSMS open with a query window with the commands you want to run in single user mode.

Click and restart the SQL service. After it has restarted [re-]open a SSMS query window and run the commands.

Once done

  • Stop SQL Service
  • Change the properties removing the -m from the startup parameters
  • Change SQL Agent to autostart and or whatever else you had to change to aget SQL into single user mode.