由于SQL Server不支持全局數據庫權限，因此需要在每個數據庫中創建用戶并授予其只讀權限。可以使用動態SQL腳本來為所有現有數據庫設置權限，具體腳本如下

##創建登陸賬號CREATE LOGIN user01 WITH PASSWORD = 'password';
##除了系統庫外給user01?db_datareader權限DECLARE @db_name NVARCHAR(128)DECLARE @sql NVARCHAR(MAX)
DECLARE db_cursor CURSOR FORSELECT nameFROM sys.databasesWHERE state_desc = 'ONLINE' AND name NOT IN ('master', 'tempdb', 'model', 'msdb','distribution')##遍歷所有在線的數據庫（排除系統數據庫） OPEN db_cursorFETCH NEXT FROM db_cursor INTO @db_name
WHILE @@FETCH_STATUS = 0BEGIN    SET @sql = 'USE [' + @db_name + '];' + CHAR(13) +???????????????'CREATE?USER?user01?FOR?LOGIN?user01;'?+?CHAR(13)?+???????????????'EXEC?sp_addrolemember?N''db_datareader'',?N''user01'';'        EXEC sp_executesql @sql##?給user01賦予db_datareader權限    FETCH NEXT FROM db_cursor INTO @db_nameEND
CLOSE db_cursorDEALLOCATE db_cursor

當然也可以使用SSMS通過界面來創建登陸名和設置權限，但是不如腳本方便。僅供參考。

其他sqlserver常用sql

查看阻塞持續時間超過5000ms的會話

SELECT W.session_id AS waiting_session_id, W.waiting_task_address, W.wait_duration_ms, W.wait_type, W.blocking_session_id, W.resource_descriptionFROM   sys.dm_os_waiting_tasks AS WWHERE W.wait_duration_ms >5000 AND W.blocking_session_id  IS NOT NULL;

查看庫內所有在wait狀態的鎖

SELECT L1.resource_type , DB_NAME(L1.resource_database_id) AS DatabaseName, CASE L1.resource_type WHEN 'OBJECT' THENOBJECT_NAME(L1.resource_associated_entity_id,L1.resource_database_id) WHEN 'DATABASE' THEN 'DATABASE' ELSE CASE WHEN L1.resource_database_id =DB_ID() THEN (SELECT OBJECT_NAME(object_id,L1.resource_database_id) FROM  sys.partitions WHERE hobt_id =L1.resource_associated_entity_id) ELSE NULL END END AS ObjectName, L1.resource_description , L1.request_session_id , L1.request_mode , L1.request_statusFROM   sys.dm_tran_locks AS L1JOIN   sys.dm_tran_locks AS L2 ON L1.resource_associated_entity_id=L2.resource_associated_entity_idWHERE  L1.request_status<>L2.request_status AND (L1.resource_description=L2.resource_description OR (L1.resource_description IS NULL  AND L2.resource_description IS NULL ) )ORDER BY L1.resource_database_id , L1.resource_associated_entity_id , L1.request_status ASC;