VC 操作 SQL Server 7.0 動態管理用戶與權限

系統存儲過程與系統表

• master..sysdatabases：所有數據庫列表
• master..syslogins：登錄賬戶 (過濾 isntname=0 排除 NT 帳戶)
• 各數據庫 sysusers：該庫授權用戶
• sp_addlogin、sp_droplogin：創建/刪除登錄賬戶
• sp_grantdbaccess、sp_revokedbaccess：授予/撤銷數據庫訪問權

關鍵操作代碼片段 (ADO)

讀取數據庫列表

_RecordsetPtr rs; rs.CreateInstance(__uuidof(Recordset));
rs->Open("select * from master..sysdatabases", cn, adOpenDynamic, adLockOptimistic, adCmdText);
while (!rs->EndOfFile) { m_dbList.AddString((char*)(_bstr_t)rs->Fields->Item["name"]->Value); rs->MoveNext(); }

讀取用戶列表

rs->Open("select * from master..syslogins where isntname=0", cn, ...);

檢查用戶權限

BOOL HasRight(CString user, CString db) {
    _bstr_t sql = "select * from " + (_bstr_t)db + "..sysusers where name='" + (_bstr_t)user + "'";
    try { rs->Open(sql, cn, ...); rs->Close(); return TRUE; }
    catch (_com_error) { return FALSE; }
}

增加用戶並授權

if (m_userList.FindString(0, user) < 0) {
    cn->Execute("sp_addlogin '" + user + "','" + user + "'", NULL, adExecuteNoRecords);
    m_userList.AddString(user);
} else {
    cn->PutDefaultDatabase(db);
    cn->Execute("sp_grantdbaccess '" + user + "'", NULL, adExecuteNoRecords);
    m_dbList.AddString(db);
}

刪除用戶/權限

if (dbList.GetCount() < 1) { // 刪除登錄賬戶
    cn->Execute("exec sp_droplogin '" + user + "'", NULL, adExecuteNoRecords);
} else { // 僅撤銷庫權限
    cn->PutDefaultDatabase(db);
    cn->Execute("exec sp_revokedbaccess '" + user + "'", NULL, adExecuteNoRecords);
}

界面截圖