SQL: Test SQLAgentReaderRole role in msdb to allow viewing jobs

\ by

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]
GO
CREATE USER [jperryx] FOR LOGIN [jperryx] 
ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [jperryx]
GO

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
GO
DROP LOGIN jperryx
GO
Posted in: Permissions, SQL Server, SQLAgent

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

\ by

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],
        [dp].[principal_id],
        [dp].[type],
        [dp].[type_desc],
        [dp].[default_schema_name],
        [dp].[create_date],
        [dp].[modify_date],
        [dp].[owning_principal_id],
        [dp].[sid],
        [dp].[is_fixed_role],
        [dp].[sid],
   --     '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]
Posted in: Coding, SQL

SQL: Audit to find identical schedules on multiple jobs

\ by

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],
		[sjs].[schedule_id]
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.

Posted in: SQL, SQLAgent \ Tagged: