Bazı kullanıcılar veritabanlarına gelen sorguları görme gereğine ihtiyaç duyabilirler.
İlgili sayfada sp_whoIsactive stored procedure’leri indirip yüklememiz lazım.
İ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;
SELECT
s.session_id,
s.login_name,
DB_NAME(s.database_id) AS database_name,
s.status AS session_status,
r.command,
r.cpu_time,
r.total_elapsed_time,
r.blocking_session_id,
r.wait_type,
r.wait_time,
r.wait_resource,
r.open_transaction_count,
s.login_time,
-- SQL Text: Aktif request varsa onu, yoksa son komutu alıyoruz
ISNULL(
SUBSTRING(q.text, (r.statement_start_offset/2) + 1,
((CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH(q.text)
ELSE r.statement_end_offset END - r.statement_start_offset)/2) + 1),
q.text
) AS sql_text,
-- Süre hesaplama (Hata buradaki eksik virgülden kaynaklanıyordu)
RIGHT('00' + CAST(DATEDIFF(SECOND, s.login_time, GETDATE()) / 86400 AS VARCHAR), 2) + '.' +
RIGHT('00' + CAST((DATEDIFF(SECOND, s.login_time, GETDATE()) % 86400) / 3600 AS VARCHAR), 2) + '.' +
RIGHT('00' + CAST((DATEDIFF(SECOND, s.login_time, GETDATE()) % 3600) / 60 AS VARCHAR), 2) + '.' +
RIGHT('00' + CAST(DATEDIFF(SECOND, s.login_time, GETDATE()) % 60 AS VARCHAR), 2) AS elapsed_time_formatted
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(ISNULL(r.sql_handle, c.most_recent_sql_handle)) AS q
WHERE s.is_user_process = 1
AND s.database_id = DB_ID() -- Sadece procedure'ün çalıştığı DB
ORDER BY r.total_elapsed_time DESC;
END;Yukarıdaki procedure’ü son kullanıcı kendi veritabanında oluşturması gerekmektedir. İki yetkiye ihtiyacı vardır kendi veritabanında oluşturulan bu procedure yapısının üzerinde execute yetkisinin olması gerekmektedir. İkinci verilmesi gereken işlem hangi kullanıcının bu procedure üzerinde işlem yapılacaksa ilgili kullanıcı login securables kısmında view server state yetkisinin verilmesi gerekmektedir. İlgili kullanıcıya master veritabanında public yetkisi verilmez.
Not: Son kullanıcı procedure’ü kendi veritabanı altında oluşturabilir. Bunun için kullanıcının db_owner ve ddl yetkisinin olması gerekmektedir. Kullanıcı kendi veritabanı altında sp_whoısactive procedure yapısını çalıştırdığında hata mesajı almaktadır. Sebebi ise master veritabanına yetkisi olmamasından dolayıdır. Sadece yetkisi olması yetmez aynı zamanda master veritabanında ilgili kullanıcının view server state yetkisinin olması gerekmektedir.
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
