Sqlserver 如何创建全局只读账号？

由于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;