交易狀態查詢

查詢目前資料庫內各Session的交易狀態以及交易量 select DTST.Session_id,DES.login_name [登入帳號],DB_NAME(DTDT.database_id) [資料庫],
DTDT.database_transaction_begin_time AS [起始時間],
DATEDIFF(ms,DTDT.database_transaction_begin_time,GETDATE()) AS [至目前耗時(ms)],
case DTAT.transaction_type when 1 then '讀取/寫入交易'
when 2 then '唯讀交易' when 3 then '系統交易'
when 4 then '分散式交易' end [交易類型],
case DTAT.transaction_state when 0 then '交易尚未完全初始化'
when 1 then '交易已經初始化，但尚未啟動'
when 2 then '交易在作用中' when 3 then '交易已經結束。它只用於唯讀交易'
when 4 then '認可處理序已經在分散式交易上起始。分散式交易在作用中，但無法再進一步處理'
when 5 then '交易是在已準備的狀態，正在等候解析。' when 6 then '已認可交易'
when 7 then '正在回復交易' when 8 then '已回復交易' end [交易狀態],
DTDT.database_transaction_log_record_count [交易紀錄(log)筆數],
DTDT.database_transaction_log_bytes_used [交易紀錄byte 數],
DTDT.database_transaction_log_bytes_reserved [交易紀錄保留byte 數],
DEST.text [最後交易文字],
DEQP.query_plan [最後查詢計畫]
from sys.dm_tran_database_transactions DTDT
JOIN sys.dm_tran_session_transactions DTST ON DTST.transaction_id=DTDT.transaction_id
JOIN sys.dm_tran_active_transactions DTAT ON DTST.transaction_id=DTAT.transaction_id
JOIN sys.dm_exec_sessions DES ON DES.session_id=DTST.session_id
JOIN sys.dm_exec_connections DEC ON DEC.session_id=DTST.session_id
LEFT JOIN sys.dm_exec_requests DER ON DER.session_id=DTST.session_id
CROSS APPLY sys.dm_exec_sql_text(DEC.most_recent_sql_handle) AS DEST
OUTER APPLY sys.dm_exec_query_plan(DER.plan_handle) AS DEQP
ORDER BY DTDT.database_transaction_log_bytes_used DESC


MSSQL資料庫權限查詢

資料庫中權限查詢 帳號與資料庫角色的從屬關係

select DB_NAME() as [DB_NAME]
, sysu.name as [登入名稱]
,case
when sysu.issqlrole =1 and sysu.islogin =0 then 'DB Role'
when sysu.issqlrole =0 and sysu.islogin =1 then 'DB Account'
else 'unKnow' End
as [AccountType]
, isnull(sysu1.name,'') as [角色名稱]
from dbo.sysusers sysu
left outer join dbo.sysmembers sysm on sysu.uid = sysm.memberuid
left outer join dbo.sysusers sysu1 on sysm.groupuid = sysu1.uid
where sysu.issqlrole = 0 and sysu.hasdbaccess = 1


帳號對結構描述的權限

SELECT DB_NAME() as [DB_NAME]
, USER_NAME(grantee_principal_id) as [登入名稱]
,case
when Prin.type ='R' then 'DB Role'
when Prin.type ='S' then 'DB Account'
else 'unKnow' End
as [AccountType]
, SCHEMA_NAME(major_id) as [SCHEMA_NAME]
, permission_name as [permission]
FROM sys.database_permissions AS Perm
JOIN sys.database_principals AS Prin
ON Perm.major_ID = Prin.principal_id AND class_desc = 'SCHEMA'


物件的權限清單

select DB_NAME() as [DB_NAME], U.name as username
,case
when U.issqlrole =1 and U.islogin =0 then 'DB Role'
when U.issqlrole =0 and U.islogin =1 then 'DB Account'
else 'unKnow' End
as [AccountType]
, CASE perm.[class]
WHEN 1 THEN obj.type_desc -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END as ObjectType
,O.name as object /* , ISNULL(col.[name],'') as ColumnName*/
, permission_name as permission
from sys.database_permissions perm
LEFT JOIN sys.columns col ON col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id]
join sys.sysusers U on perm.grantee_principal_id = uid
join sys.sysobjects O on perm.major_id = id order by U.name