I'm new to Brent Ozar's sp_blitz and am trying to do some cleanup in my new job. I get tons of security findings about agent jobs owned by users. Most of these hits are system-type accounts (reportexec, webexec, sqlservice), rather than employee-associated accounts. Is there a problem with this approach? I'd love to reduce the number of hits I'm getting with sp_blitz, but am not sure it's worth changing some of these things that were purposely set up like this. Thanks for any help.
Sql-server – TSQL Agent jobs owned by users
sp-blitzsql-server-agentt-sql
Related Solutions
The following T-SQL will enable Database Mail in SQL Server Agent:
USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail',
N'REG_DWORD', 1
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile',
N'REG_SZ', N'<profilename>'
GO
For future reference, you can easily determine this by expanding the "SQL Server Agent" node in SQL Server Management Studio's Object Explorer pane. Then right-click on "SQL Server Agent", click "Properties", select "Alert System", enter the appropriate details, and click on "Script":
If you are using Windows Authentication in the linked server setup it will not work using the SQL Server agent even if you have Kerberos correctly setup. You can configure the linked server to use SQL authentication for those connections but I would recommend to create a SSIS package for this as the authentication will be easier to configure.
Can you please post the linked server configuration for further information?
Related Question
- Sql-server – SQL Agent Proxy – writing to logs is only allowed to jobs that are owned by sysadmin
- Sql-server – SQL Server Agent Jobs – Long running job delaying other jobs
- SQL Server – Always On: SQL Agent Running Stored Proc on Inactive Server
- Sql-server – SQL Agent Jobs hanging at last Run date and time
- Sql-server – SQL Agent using domain user for Jobs with TSQL
Best Answer
You should be fine as sp_blitz will highlight if jobs are owned by users since if their login is disabled or not available due to Active Directory problems, the job will stop working.
you can create your skip table and specify that to the sp_blitz
@SkipChecksTableto skip checks.BTW, sp_blitz is on github and you can view the code as well.