Report all users in server databases with dbo permissions

This is from an idea from here.

IF OBJECT_ID('tempdb.dbo.#t1') IS NOT NULL
	DROP TABLE #t1

SELECT  CAST('?' AS VARCHAR(128)) AS [Database Name],
        [su1].[name] AS [Database User Name],
        [su2].[name] AS [Database Role]
INTO    [#t1]
FROM    [sys].[database_role_members] [r]
INNER JOIN .[sysusers] [su1]
ON      [su1].[uid] = [r].[member_principal_id]
INNER JOIN .[sysusers] [su2]
ON      [su2].[uid] = [r].[role_principal_id]
WHERE   [su2].[name] IN ('db_owner')
        AND [su1].[name] NOT IN ('dbo')
        AND 1 = 2

INSERT [#t1]
        ([Database Name],
		[Database User Name],
         [Database Role]
        ) 
EXEC sp_MSForEachDB 'SELECT ''?'' AS [Database Name], su1.name AS [Database User Name], su2.name AS [Database Role]
						FROM [?].sys.database_role_members r
							INNER JOIN [?]..sysusers su1 ON su1.[uid] = r.member_principal_id
							INNER JOIN [?]..sysusers su2 ON su2.[uid] = r.role_principal_id
						WHERE su2.name IN(''db_owner'') AND su1.name NOT IN(''dbo'')'
DELETE
FROM [#t1]
WHERE [Database Name] IS NULL

SELECT [Database Name],
       [Database User Name],
       [Database Role]
FROM [#t1]

GO

Report on SQLAgent role members

Just remove the filter to see everyone.


;WITH    [RoleMembers]([member_principal_id], [role_principal_id])
          AS (
              SELECT    [rm1].[member_principal_id],
                        [rm1].[role_principal_id]
              FROM      [msdb].[sys].[database_role_members] [rm1] (NOLOCK)
              UNION ALL
              SELECT    [d].[member_principal_id],
                        [rm].[role_principal_id]
              FROM      [msdb].[sys].[database_role_members] [rm] (NOLOCK)
              INNER JOIN [RoleMembers] AS [d]
              ON        [rm].[member_principal_id] = [d].[role_principal_id]
             )
    SELECT DISTINCT
            [rp].[name] AS [database_role],
            [mp].[name] AS [database_user],
            [mp].[type]
    FROM    [RoleMembers] [drm]
    JOIN    [msdb].[sys].[database_principals] [rp]
    ON      ([drm].[role_principal_id] = [rp].[principal_id])
    JOIN    [msdb].[sys].[database_principals] [mp]
    ON      ([drm].[member_principal_id] = [mp].[principal_id])
    WHERE   [rp].[name] LIKE 'SQLAgent%'
            AND [mp].[name] NOT LIKE '##%'
    ORDER BY [rp].[name]


This should produce output similar to this:

 or

database_role	database_user	type
SQLAgentOperatorRole	PolicyAdministratorRole	R
SQLAgentReaderRole	PolicyAdministratorRole	R
SQLAgentReaderRole	SQLAgentOperatorRole	R
SQLAgentUserRole	dc_admin	R
SQLAgentUserRole	dc_operator	R
SQLAgentUserRole	MS_DataCollectorInternalUser	S
SQLAgentUserRole	PolicyAdministratorRole	R
SQLAgentUserRole	SQLAgentOperatorRole	R
SQLAgentUserRole	SQLAgentReaderRole	R

SHOWPLAN permissions

One can be database owner, SHOWPLAN within the database, CONTROL Database, CONTROL Server, or ALTER TRACE at the server level.

See Rob Farley’s article for a script to run a test.

Rob Farley : SHOWPLAN permission denied even if the database isn’t actually used

To view a query plan, you need SHOWPLAN permission on the database level at least. You have this if you have CONTROL DATABASE, or CONTROL SERVER, or if you have ALTER TRACE at the instance level. I know this last one because it’s mentioned in Books Online

Source: sqlblog.com/blogs/rob_farley/archive/2015/04/14/showplan-permission-denied-even-if-the-database-isn-t-actually-used.aspx

ERROR: SSPI handshake failed with error code … Error: 18452

We were trying to connect a remote SQL publisher to a local subscriber and on the subscriber get these errors, found in the SQL Errorlog.

Date		12/8/2016 6:52:42 AM
Log		SQL Server (Current - 12/7/2016 6:03:00 PM)

Source		Logon

Message
Error: 17806, Severity: 20, State: 14.

----

Date 12/8/2016 6:52:42 AM
Log SQL Server (Current - 12/7/2016 6:03:00 PM)

Source Logon

Message
SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed [CLIENT: 172.31.5.158]

----
Date		12/8/2016 6:52:42 AM
Log		SQL Server (Current - 12/7/2016 6:03:00 PM)

Source		Logon

Message
Error: 18452, Severity: 14, State: 1.

----
Date		12/8/2016 6:52:42 AM
Log		SQL Server (Current - 12/7/2016 6:03:00 PM)

Source		Logon

Message
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: xxx.xx.x.xxx]

----

However, the SQL login was coming from a domain that already HAD replication running on another database.

It turned out to be an incorrect login name, rather than an untrusted domain.

Never trust an error message.

TSQL: Find characters in one string missing from another

I have a set of characters, all of which must match the characters in another set.

e.g. All characters in “RW” must exist in “RWCE”.

There’s probably an easier way, but here’s one solution.

  1. Create a CTE table of the string to test, converting a set of letters to rows.
  2. Return any rows of just the missing or failed characters
  3. If a row EXISTS we want to report the error.

DECLARE @vcNewPermissions VARCHAR(128)
SELECT @vcNewPermissions = 'RW'
--
IF OBJECT_ID('tempdb.dbo.[#IllegalPermissions]') IS NOT NULL
    DROP TABLE [#IllegalPermissions]
--
;WITH    [mycte]([x])
          AS (
              SELECT    SUBSTRING([a].[FindString], [v].[number] + 1, 1)
              FROM      (
                         SELECT @vcNewPermissions AS [FindString]
                        ) [a]
              JOIN      [master].[dbo].[spt_values] [v]
              ON        [v].[number] < LEN([a].[FindString])
              WHERE     [v].[type] = 'P'
             )
    SELECT  [x]
    INTO    [#IllegalPermissions]
    FROM    [mycte]
    WHERE   CHARINDEX([x], 'RWCESVXT') = 0

IF EXISTS ( SELECT  *
            FROM    [#IllegalPermissions] )
    RAISERROR('ERROR: permission not found',16,1)

Msg 11520, Level 16, State 1

Msg 11520, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement ‘EXECUTE master.dbo.xp_sqlagent_is_starting @retval OUTPUT’ in procedure ‘sp_is_sqlagent_starting’ invokes an extended stored procedure.

In SQL 2012 sp_help_job has changed. See this solution:

Workaround: SQL Server 2012 – OPENROWSET on sp_help_job throws “The metadata could not be determined”

Issue:In SQL 2012, stored procedures that call any other stored procedures or extended stored procedures that return result sets should specify WITH RESULT SETSSQL Agent’s stored procedure sp_help_job  – returns single resultset if no parameters are passed to stored procedure.  – returns 3 resultsets if job_id or job_name is specified. MSDN linkWhen RESULTSET is not described,…

Source: blogs.msdn.microsoft.com/sqlagent/2012/07/12/workaround-sql-server-2012-openrowset-on-sp_help_job-throws-the-metadata-could-not-be-determined/

PowerShell: Check all servers for failed jobs.

This is a two part process.

First we use our [master_admin].[dbo].[rp_JobFailures] stored procedure to report any failures on the server. Here’s the guts of our procedure.

DECLARE @nDaysAgo INT
SELECT  @nDaysAgo = 1
SELECT  CAST([j].[name] AS VARCHAR(128)) [FAILED_job_name],
        LEFT(DATENAME(dw, CAST([run_date] AS CHAR(8))) + SPACE(1)
             + CAST([master_admin].[dbo].[fn_INTtoDateTime]([h].[run_date], [h].[run_time]) AS VARCHAR), 30) [RunDate],
        CASE [run_status]
          WHEN 0 THEN 'Failed'
          WHEN 1 THEN 'Succeeded'
          WHEN 2 THEN 'Retry'
          WHEN 3 THEN 'Canceled'
          WHEN 4 THEN 'In progress'
        END [job_status],
        (ISNULL(MAX([s].[step_id]), '')) [StepID],
        (ISNULL(MAX([s].[step_name]), '')) [StepName],
        (ISNULL(MAX([command]), MAX([h].[message]))) [StepCommand],
        MAX([operator_id_emailed]) [NotifiedID]
FROM    [msdb]..[sysjobhistory] [h]
JOIN    [msdb]..[sysjobs] [j]
ON      [j].[job_id] = [h].[job_id]
JOIN    [msdb]..[syscategories] 1
ON      [j].[category_id] = 1.[category_id]
LEFT OUTER JOIN (
                 -- All jobsteps that have a final history job outcome of
	SELECT  [s].[step_name],
            [s].[command],
            [s].[step_id],
            [s].[job_id],
            [last_run_outcome]
    FROM    [msdb]..[sysjobsteps] [s] WITH (NOLOCK)
    LEFT OUTER JOIN (
                     SELECT [job_id]
                     FROM   [msdb]..[sysjobhistory] [h]
                     WHERE  [run_status] = 0
                            AND [h].[run_date] >= REPLACE(CONVERT(VARCHAR(10), DATEADD(DAY, -@nDaysAgo, GETDATE()), 121),
                                                          '-', '')
                    ) [okay]
    ON      [okay].[job_id] = [s].[job_id]
    WHERE   [okay].[job_id] IS NOT NULL
            AND [last_run_date] >= REPLACE(CONVERT(VARCHAR(10), DATEADD(DAY, -@nDaysAgo, GETDATE()), 121), '-', '')
                ) [s]
ON      [h].[job_id] = [s].[job_id]
        AND [h].[step_id] = [s].[step_id]
WHERE   [h].[run_date] >= REPLACE(CONVERT(VARCHAR(10), DATEADD(DAY, -@nDaysAgo, GETDATE()), 121), '-', '')
        AND [run_status] NOT IN (1, 4)
GROUP BY [j].[name],
        [run_date],
        [run_time],
        [run_status]
ORDER BY [h].[run_date],
        [h].[run_time],
        [j].[name] 

Then we use PowerShell to run that procedure on each of our SQL Servers, stored in $AllServersList.

# ------------------------------------------------------------------------------------------------------------------------
# What jobs failed since yesterday?

$TSQL = 'EXEC [master_admin].[dbo].[rp_JobFailures] @nDaysAgo=1, @vcSkipJob=NULL'

$VerbosePreference="Continue"
$bDEBUG = $true
#	$bDEBUG = $false ; $VerbosePreference="SilentlyContinue" ; $dt=@()
$AllServersList | %{
	$CurrentServer = $_
	Write-Host -ForegroundColor Blue "$(Get-Date -Format g)	[$CurrentServer]"
	Write-Verbose "	Invoke-sqlcmd2 -ConnectionTimeout 10 -ServerInstance $_ -Query ""$TSQL"""; 

	if (!($ExclusionList -contains $CurrentServer)) {
		if ($bDEBUG -eq $false) {
			$dr = Invoke-sqlcmd2 -ConnectionTimeout 10 -ServerInstance $CurrentServer -Query "$TSQL" -As DataRow
			if ($dr -ne $null) {
				$dr | add-member -name ServerName -type noteproperty -value "$CurrentServer"
				$dt += $dr
			}
		}
	} else {
		Write-Host -ForegroundColor Red "Skipping $CurrentServer $_"
	}
}

$dt |Select-Object ServerName, RunDate, Failed_Job_Name, StepID, StepName, StepCommand |ft -AutoSize

You can see I like to run Foreach without doing anything initially to make sure I got it right. Then highlighting from the commented $bDEBUG line to the end gets us what we want. I also use an exclusion list which is simply an array of server names to bypass.

The SQL purists will suggest we just run it within SSMS. Just create server groups for all your servers and right-click and run the query.

SQL Backups reviewing LSNs

Here’s a nice page with some code which shows how to build a database test case sample where you can run your own tests.

How do I map a differential/transaction log backup to its full backup without using MSDB?

If you have multiple full, differential and transaction log backups, there are chances you may end up facing the error below during the restore process. “This differential backup cannot be restored because the database has not been restored to the correct earlier state”   You know that any differential/T-log  backup is mapped to particular full…

Source: blogs.msdn.microsoft.com/sqlserverfaq/2013/05/22/how-do-i-map-a-differentialtransaction-log-backup-to-its-full-backup-without-using-msdb/

I like this site’s simple list of rules which shows the FULL backup and where the CheckpointLSN and FirstLSN could be found in the DIFF and LOG backups.

Considering SQL Server Log Sequence Numbers for Backups

Take a look at this blog and understand SQL Server log sequence numbers for backups and know why LSN is important while restoring the database

Source: www.sqlmvp.org/sql-server-log-sequence-numbers-for-backups/

This page has nice graphics following the LSNs.

Understanding SQL Server Log Sequence Numbers for Backups

This tip describes SQL Server Log Sequence Numbers (LSNs) and how the sequence numbers link full, differential and transaction log backups. We will look at sample backups and how these LSN values can be read from the backup files to determine the restore

Source: www.mssqltips.com/sqlservertip/3209/understanding-sql-server-log-sequence-numbers-for-backups/

SQL: Get last queries executed on a server

Use this to see what you or others were recently doing.


USE [master]
GO
SELECT [execquery].[last_execution_time],
[execsql].1  
FROM [sys].[dm_exec_query_stats] AS [execquery]
CROSS APPLY [sys].[dm_exec_sql_text]([execquery].[sql_handle]) AS [execsql]
WHERE [last_execution_time] > DATEADD(HOUR, -1, GETDATE())
AND [execsql].1 LIKE '%execq%'
ORDER BY [execquery].[last_execution_time] DESC