SQL Server 新建只读查询用户（Read-Only Query User）
基础脚本 创建一个只能执行 SELECT 查询的只读用户，标准做法如下： CREATE LOGIN onlyque WITH PASSWORD = '123456'; USE test; GO CREATE USER onlyque FOR LOGIN onlyque; USE test; GO GRANT SELECT ON SCHEMA::dbo TO onlyque; 这个脚本能跑通，但在生产环境下存在几个风险。 生产环境强化版 -- ============================================ -- Create Read-Only Query User for test database -- ============================================ -- Step 1: Create login at server level (with strong password) IF NOT EXISTS (SELECT 1 FROM sys.sql_logins WHERE name = 'onlyque') BEGIN CREATE LOGIN onlyque WITH PASSWORD = 'YourStr0ngP@ss2026' , DEFAULT_DATABASE = test , CHECK_POLICY = ON , CHECK_EXPIRATION = ON; PRINT 'Login [onlyque] created.'; END ELSE PRINT 'Login [onlyque] already exists.'; GO -- Step 2: Create database user USE test; GO IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'onlyque') BEGIN CREATE USER onlyque FOR LOGIN onlyque WITH DEFAULT_SCHEMA = dbo; PRINT 'User [onlyque] created in [test].'; END ELSE PRINT 'User [onlyque] already exists in [test].'; GO -- Step 3: Grant read-only permissions GRANT SELECT ON SCHEMA::dbo TO onlyque; -- Step 4: Explicitly deny write operations (defense in depth) DENY INSERT, UPDATE, DELETE ON SCHEMA::dbo TO onlyque; GO -- Step 5: Verify permissions SELECT dp.name AS principal_name, dp.type_desc, perm.permission_name, perm.state_desc, OBJECT_SCHEMA_NAME(perm.major_id) AS schema_name FROM sys.database_principals dp JOIN sys.database_permissions perm ON dp.principal_id = perm.grantee_principal_id WHERE dp.name = 'onlyque'; GO 关键改进点 1. 密码策略 — 拒绝弱口令 CHECK_POLICY = ON 强制使用 Windows 密码复杂度策略（大写 + 小写 + 数字 + 特殊字符，最少 8 位）。生产环境永远不要用 123456。 ...