Views: 0

這篇提供懶人包參考，大致上是在測試資料庫建立一個唯讀帳號，然後再將連線字串給AI，AI就能讀取了，然後資料庫對開發機的防火牆要開啟。

先說結論．．．我自己在SSMS直接設定一個唯讀帳號其實比叫AI弄還快

我自己弄大概兩分鐘就會弄好了，這次Codex GPT5.5 AI在這方面花了半小時還搞不好

這篇的內容大家加減看就好

產生測試環境唯讀帳號

有了AI，我們當然要叫AI幹活，直接請他產生SQL對應指令，關鍵點是要跟他說明環境SQL Server 2016

提示詞:
直接給我指令碼
建立讓AI唯讀取測試環境資料庫的專用帳號
環境是SQL Server 2016
把資料庫名稱 使用者名稱 密碼 都提取到前面變成變數
LoginName 跟 UserName相同就好

產生出來的成果

1. 建 Login
2. 建 DB User
3. ALTER USER … WITH LOGIN … 修 SID
4. GRANT CONNECT
5. 加 db_datareader
6. GRANT VIEW DEFINITION
7. DENY 寫入/執行
8. 驗證 SID / role / permission

--建立LOGIN
DECLARE @DatabaseName sysname = N'UOFTEST';--資料庫名稱
DECLARE @AccountName sysname = N'AI_AGENT_READONLY';--帳號名稱
DECLARE @Password nvarchar(128) = N'請填入密碼';--密碼

DECLARE @Sql nvarchar(max);

USE [master];

IF NOT EXISTS (
    SELECT 1
    FROM sys.sql_logins
    WHERE name = @AccountName
)
BEGIN
    SET @Sql = N'CREATE LOGIN ' + QUOTENAME(@AccountName) +
               N' WITH PASSWORD = ' + QUOTENAME(@Password, '''') +
               N', CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF;';
    EXEC sp_executesql @Sql;
END;
GO

USE [UOFTEST];
GO

IF NOT EXISTS (
    SELECT 1
    FROM sys.database_principals
    WHERE name = N'AI_AGENT_READONLY'
)
BEGIN
    CREATE USER [AI_AGENT_READONLY] FOR LOGIN [AI_AGENT_READONLY];
END;
GO

ALTER USER [AI_AGENT_READONLY] WITH LOGIN = [AI_AGENT_READONLY];
GO

GRANT CONNECT TO [AI_AGENT_READONLY];
GO

ALTER ROLE [db_datareader] ADD MEMBER [AI_AGENT_READONLY];
GO

GRANT VIEW DEFINITION TO [AI_AGENT_READONLY];
GO

USE [UOFTEST];
GO

IF NOT EXISTS (
    SELECT 1
    FROM sys.database_principals
    WHERE name = N'AI_AGENT_READONLY'
)
BEGIN
    CREATE USER [AI_AGENT_READONLY] FOR LOGIN [AI_AGENT_READONLY];
END;
GO

ALTER USER [AI_AGENT_READONLY] WITH LOGIN = [AI_AGENT_READONLY];
GO

GRANT CONNECT TO [AI_AGENT_READONLY];
GO

ALTER ROLE [db_datareader] ADD MEMBER [AI_AGENT_READONLY];
GO

GRANT VIEW DEFINITION TO [AI_AGENT_READONLY];
GO

建議補上明確禁止寫入：

USE [UOFTEST];
GO

DENY INSERT TO [AI_AGENT_READONLY];
DENY UPDATE TO [AI_AGENT_READONLY];
DENY DELETE TO [AI_AGENT_READONLY];
DENY EXECUTE TO [AI_AGENT_READONLY];
DENY ALTER TO [AI_AGENT_READONLY];
DENY CONTROL TO [AI_AGENT_READONLY];
GO

驗證 SID：

SELECT
    dp.name AS DbUser,
    dp.sid AS DbUserSid,
    sp.name AS LoginName,
    sp.sid AS LoginSid,
    CASE WHEN dp.sid = sp.sid THEN 'OK' ELSE 'SID_NOT_MATCH' END AS SidStatus
FROM [UOFTEST].sys.database_principals dp
LEFT JOIN master.sys.server_principals sp
    ON dp.sid = sp.sid
WHERE dp.name = N'AI_AGENT_READONLY';

驗證權限：

USE [UOFTEST];
GO

SELECT
    pr.name AS PrincipalName,
    pe.state_desc,
    pe.permission_name
FROM sys.database_permissions pe
JOIN sys.database_principals pr
    ON pe.grantee_principal_id = pr.principal_id
WHERE pr.name = N'AI_AGENT_READONLY'
ORDER BY pe.permission_name;

驗證角色：

USE [UOFTEST];
GO

SELECT
    dp.name AS UserName,
    rp.name AS RoleName
FROM sys.database_role_members drm
JOIN sys.database_principals rp
    ON drm.role_principal_id = rp.principal_id
JOIN sys.database_principals dp
    ON drm.member_principal_id = dp.principal_id
WHERE dp.name = N'AI_AGENT_READONLY';

實際能用嗎?

一開始我請AI自己測試登入

無法開啟登入所要求的資料庫 "UOFTEST"。登入失敗。
使用者 'AI_AGENT_READONLY' 的登入失敗。

後來的解決方法是

我到UOFTEST資料庫底下的登入環境，把AI_AGENT_READONLY重新授予dbreader權限就好了…