Bazı kullanıcılar veritabanlarına gelen sorguları görme gereğine ihtiyaç duyabilirler.
Aşağıdaki sayfada sp_whoIsactive stored procedure’leri indirip yüklememiz lazım.
sp_whoisactive SQL Server Monitoring Stored Procedure Downloads
İlk başta normal sysadmin kullanıcısında instance üzerinde sp_whoIsactive çalıştırmamız lazım gelen connectionlardaki sorguları görebiliriz.
Önceden oluşturmuş olduğum kullanıcı ile login olduktan sonra sp_whoisactive’i çalıştırdıktan sonra hata ile karşılaştım.
dmw(dynamic Management View)’ler ve dmf(dynamic management functions)’ler vasıtasıyla bu sorguları loginler görebilir. Fakat dmw’leri ve dmf’leri sorgulayabilmek için ilgili login’e server seviyesinde VIEW_SERVER_STATE yetkisinin verilmesi gerekir.
USE [master]
GO
GRANT VIEW SERVER STATE TO [Loginİsmi]
Login’e bu yetkiyi verdiğimizde kendi veritabanının dışında o instance üzerinde bulunan diğer veritabanlarının sorgularınıda görebilecektir.
Bu ifade SSMS arayüzünden de yapılabilmektedir. Aşağıdaki resimlerde arayüzden nasıl yapıldığı gözükmektedir.
AGENT1 kullanıcına sağ tıklayıp properties dedikten sonra securables kısmından instance seviyesinde view server state yetkisi verilmektedir.
Server seviyesinde AGENT1 kullanıcısına yetki vermiş olduk.
İşlemin script’ini alıp yetkimizi vermiş oluyoruz.
use [master]
GO
GRANT VIEW SERVER STATE TO [AGENT1]
GO
Tekrar sp_whoisactive’i çalıştırdığımızda yukarıdaki hatayı vermiş oldu.
S1\Administrator kullanıcısıyla login olduktan sonra Master veritabanı altında bulunan dbo.sp_WhoIsactive stored procedure’üne sağ tıklayıp yetki vermemiz gerekmektedir. Çünkü AGENT1 Logine ne kadar da view server state yetkisi versekde bu login belirtilen stored procedure’ü çalıştıramaz.
İlgili login mevcut olan stored procedure’e ulaşabilmemiz için ilk yapılması gereken stored procedure’ün olduğu master veritabanına public yetkisi verilmesi gerekiyor. Sql agent içinde msdb veritabanına vermiştik hatırlarsak.
Master veritabanına ilgili login’i public olarak yetkilendirdikten sonra master veritabanı altındaki stored procedure’e sağ tıklayıp properties’dan permissions bölümünden ilgili login’i seçtikten sonra execute yetkisi verilmesi lazım.
Bu yetkilendirilmeden sonra AGENT1 kullanıcısıyla login olduktan sonra sp_WhoIsactive’i çalıştırabildiğimizi görüyoruz.
Yukarıda dikkat edersenız kullanıcı instance altında bulunan tüm sorgu sonuçlarını dönmüş oldu sadece yetkili olduğu veritabanı üzerinde çalışan sorguları bekleme zamanını görmek isterse aşağıdaki stored procedure’ün ilgili veritabanı altında oluşturup ilgili login’e bu stored
procedure çalıştırma yetkisi verilirse sadece yetkili olduğu veritabanı üzerinde çalışan sorguları görmektedir. İlgili stored procedure:
CREATE PROCEDURE
dbo.GetActiveConnectionsForCurrentDB
AS
BEGIN
SET NOCOUNT ON;
-- Aktif bağlantıları listele
SELECT
c.session_id AS session_id,
s.login_name AS login_name,
DB_Name(s.database_id) AS database_name,
s.status AS session_status,
r.command AS command,
r.cpu_time AS cpu_time,
r.total_elapsed_time AS
total_elapsed_time,
-- Bekleme bilgileri
r.blocking_session_id AS
blocking_session_id,
r.wait_type AS wait_type,
r.wait_time AS wait_time,
r.wait_resource AS wait_resource,
r.open_transaction_count AS
open_transaction_count,
s.login_time AS login_time,
-- SQL Text
SUBSTRING(q.text,
(r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset
WHEN -1 THEN
LEN(CONVERT(NVARCHAR(MAX), q.text)) * 2
ELSE
r.statement_end_offset
END -
r.statement_start_offset) / 2) + 1) AS sql_text,
-- Çalışma süresi, DD.HH.MM.SS.MSS
formatında
RIGHT('00' + CAST(DATEDIFF(MILLISECOND,
s.login_time, GETDATE()) / 86400000 AS
VARCHAR(2)), 2) + '.' +
RIGHT('00' + CAST((DATEDIFF(MILLISECOND,
s.login_time, GETDATE()) % 86400000) / 3600000 AS
VARCHAR(2)), 2) + '.' +
RIGHT('00' + CAST((DATEDIFF(MILLISECOND,
s.login_time, GETDATE()) % 3600000) / 60000 AS
VARCHAR(2)), 2) + '.' +
RIGHT('00' + CAST((DATEDIFF(MILLISECOND,
s.login_time, GETDATE()) % 60000) / 1000 AS
VARCHAR(2)), 2) + '.' +
RIGHT('000' + CAST(DATEDIFF(MILLISECOND,
s.login_time, GETDATE()) % 1000 AS VARCHAR(3)),
3) AS elapsed_time
FROM
sys.dm_exec_sessions AS s
INNER JOIN
sys.dm_exec_connections AS c
ON s.session_id = c.session_id
LEFT JOIN
sys.dm_exec_requests AS r
ON s.session_id = r.session_id
OUTER APPLY
sys.dm_exec_sql_text(r.sql_handle) AS q
WHERE
s.is_user_process = 1 -- Yalnızca
kullanıcı bağlantıları
AND s.database_id = DB_ID() -- Şu anki
veritabanındaki bağlantılar
ORDER BY
r.total_elapsed_time DESC; -- En uzun
süre çalışanları üstte göster
END;Herhangi bir view server state yetkisine gerek yoktur..
Başka bir makalede görüşmek dileğiyle.
“Bu böyledir, zira Allah hakikatin kendisidir; O’nun dışında taptıkları şeyler ise asılsızdır ve Allah, yalnızca O, çok yücedir, çok büyüktür.”Lokman-30
