Bu makalede mssql server üzerinde sysadminlerin belirli aralıklarla kontrol edilip son kullanıcıya mail ile bildirilmesi sağlanacaktır. Bu yapı ile izinsiz veya yanlışlıkla verilmiş sysadminleri görebiliriz.
İlk olarak stored procedure yapımızı oluşturuyoruz.
--********************* STORED PROCEDURE **************************
USE [DBAMON]
GO
CREATE PROCEDURE [dbo].[SysAdminLoginsReport]
AS
BEGIN
declare @emailSubject varchar(100),
@textTitle varchar(100),
@tableHTML nvarchar(max),
@body nvarchar(max),
@emailText varchar(max),
@ServerName varchar(max)
Declare @email varchar(max)= 'Operator mail adresi veya başka bir mail'
select @ServerName = @@ServerName
DECLARE @AGRole varchar(100)
SELECT @AGRole = CASE
WHEN (a.role = 1) THEN 'PRIMARY SITE'
WHEN (a.role = 2) THEN 'SECONDARY SITE'
ELSE 'UNKNOWN AG ROLE!'
END
FROM sys.dm_hadr_availability_replica_states AS a JOIN sys.availability_replicas AS b
ON b.replica_id = a.replica_id left outer join sys.dm_hadr_name_id_map as grp on grp.ag_id = a.group_id
WHERE replica_server_name = @ServerName
select @emailSubject = @ServerName +'[' + @AGRole +']' + ' Sysadmin Logins Report'
/****** SQL SERVER SYSADMINS *******/
select @textTitle = 'SQL SERVER SYSADMINS'
set @tableHTML = '<html><head><style>' +
'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +
'</style></head><body>' +
'<div style="margin-top:20px; margin-left:5px; margin-bottom:15px; font-weight:bold; font-size:1.3em; font-family:calibri;">' +
@textTitle + '</div>' +
'<div style="margin-left:50px; font-family:Calibri;"><table cellpadding=0 cellspacing=0 border=0' +
'<tr bgcolor=#4b6c9e>' +
'<td align=center><font face="calibri" color=White><b>Login Name</b></font></td>' +
'<td align=center><font face="calibri" color=White><b>Type</b></font></td>' +
'<td align=center><font face="calibri" color=White><b>Is Disabled</b></font></td></tr>'
select @body =
(
select ROW_NUMBER() over(order by name) % 2 as TRRow,
td = name ,
td = type_desc,
td = is_disabled
FROM master.sys.server_principals (nolock)
WHERE IS_SRVROLEMEMBER ('sysadmin',name) = 1
ORDER BY name
for XML raw('tr') , elements
)
set @body = REPLACE(@body, '<td>', '<td align=center><font face="calibri">')
set @body = REPLACE(@body, '</td>', '</font></td>')
set @body = REPLACE(@body, '_x0020_', space(1))
set @body = Replace(@body, '_x003D_', '=')
set @body = Replace(@body, '<tr><TRRow>0</TRRow>', '<tr bgcolor=#F8F8FD>') set @body = Replace(@body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#EEEEF4>')
set @body = Replace(@body, '<TRRow>0</TRRow>', '')
set @tableHTML = @tableHTML + @body + '<tr></tr></table></div>'
set @emailText = @tableHTML;
set @emailText = @emailText + '</body></html>'
set @emailText = '<div style="color:Black; font-size:11pt; font-family:Calibri; width:100px;">' + @emailText + '</div>'
if (@emailText is not null)
begin
exec msdb.dbo.sp_send_dbmail
@profile_name = 'Profile_Name',
@recipients = @email,
@body = @emailText,
@subject = @emailSubject,
@body_format = 'HTML';
end
END
GO
Stored Procedure yapımızı oluşturduktan sonra sql agent altında job’ımızı oluşturuyoruz. İlgili job’ın create script’i görülmektedir.
--********************* JOB **************************
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA - SysadminLoginReport',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Control] Script Date: 8/24/2025 5:22:34 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Control',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXECUTE [dbo].[SysAdminLoginsReport] ',
@database_name=N'DBAMON',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'SCH-SysAdminLoginsReport',
@enabled=1,
@freq_type=16,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20190909,
@active_end_date=99991231,
@active_start_time=120000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Oluşturulan job’ımız çalıştırıldığında aşağıdaki hata mesajıyla karşılaşılır
Message
Executed as user: JNDINT\khibsvts00. Mail not queued. Database Mail is stopped. Use sysmail_start_sp to start Database Mail. [SQLSTATE 42000] (Error 14641). The step failed
Yukarıdaki hata mesajıyla karşılaşılırsa ilgili makale sorunu çözmüş olacaktır.
İlgili sorun çözüldükten sonra belirtilen mail adresine hangi kullanıcıların sysadmin olduğunu görebiliriz.
Ey iman edenler, sabırla ve namazla yardım dileyin. Gerçekten Allah, sabredenlerle beraberdir. Bakara Suresi, 153. Ayet
