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 FOR
SELECT name
FROM sys.databases
WHERE state_desc = 'ONLINE' AND name NOT IN ('master', 'tempdb', 'model', 'msdb','distribution')
##遍历所有在线的数据库（排除系统数据库） 
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db_name


WHILE @@FETCH_STATUS = 0
BEGIN
    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_name
END


CLOSE db_cursor
DEALLOCATE 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_description
FROM   sys.dm_os_waiting_tasks AS W
WHERE 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_status
FROM   sys.dm_tran_locks AS L1
JOIN   sys.dm_tran_locks AS L2 ON L1.resource_associated_entity_id
=L2.resource_associated_entity_id
WHERE  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;