本文主要通過調用SQL Server
的系統存儲過程和系統表
在程序中動態增加和刪除用戶
一
首先用系統管理員帳號登錄系統
程序省略
請看源程序
二
進入用戶管理界面
讀取數據庫
從master
sysdatabse中讀取所有數據庫
void CUser::AddDataBase()
{
_RecordsetPtr rs;
_bstr_t bt;
HRESULT hr;
CString str;
bt=(_bstr_t)
select * from master
sysdatabases
;
try{
hr=rs
CreateInstance (__uuidof(Recordset));
ASSERT(SUCCEEDED(hr));
//打開數據源
hr=rs
>Open (bt
cn
GetInterfacePtr ()
adOpenDynamic
adLockOptimistic
adCmdText);
ASSERT(SUCCEEDED(hr));
rs
>MoveFirst();
while(!rs
>EndOfFile)
{
str=(char*)(_bstr_t)rs
>Fields
>GetItem(
name
)
>Value;
m_database
AddString(str);
rs
>MoveNext();
}
rs
>Close();
m_database
SetCurSel(
);
}
catch(_com_error)
{}
}
讀取用戶
從master
syslogins視圖中讀取所有非NT用戶
void CUser::AddUser()
{
_RecordsetPtr rs;
_bstr_t bt;
HRESULT hr;
CString str;
bt=(_bstr_t)
select * from master
syslogins where isntname=
;
try{
hr=rs
CreateInstance (__uuidof(Recordset));
ASSERT(SUCCEEDED(hr));
//打開數據源
hr=rs
>Open (bt
cn
GetInterfacePtr ()
adOpenDynamic
adLockOptimistic
adCmdText);
ASSERT(SUCCEEDED(hr));
rs
>MoveFirst();
while(!rs
>EndOfFile)
{
str=(char*)(_bstr_t)rs
>Fields
>GetItem(
name
)
>Value;
m_user_list
AddString(str);
rs
>MoveNext();
}
rs
>Close();
m_user_list
SetCurSel (
);
}
catch(_com_error)
{}
}
讀取當前用戶有權限的數據庫
每個數據庫有權限的用戶在每個數據的sysusers表中
void CUser::OnSelchangeUserList()
{
CString strUser
strDB;
m_db_list
ResetContent ();
m_user_list
GetText (m_user_list
GetCurSel ()
strUser);
for(int i=
;i<m_database
GetCount ();i++)
{
m_database
GetLBText (i
strDB);
if(HasRight(strUser
strDB))
{
m_db_list
AddString (strDB);
}
}
if(m_db_list
GetCount ()>
)
{
m_db_list
SetCurSel (
);
}
}
//查看用戶user有沒有數據庫database的權限
BOOL CUser::HasRight(CString user
CString database)
{
_RecordsetPtr rs;
_bstr_t bt;
HRESULT hr;
BOOL bResult=FALSE;
bt=(_bstr_t)
select * from
+(_bstr_t)database+(_bstr_t)
sysusers where name=
+(_bstr_t)user+(_bstr_t)
;
try{
hr=rs
CreateInstance (__uuidof(Recordset));
ASSERT(SUCCEEDED(hr));
//打開數據源
hr=rs
>Open (bt
cn
GetInterfacePtr ()
adOpenDynamic
adLockOptimistic
adCmdText);
ASSERT(SUCCEEDED(hr));
rs
>MoveFirst();
rs
>Close();
bResult=TRUE;
}
catch(_com_error)
{
}
return bResult;
}
增加用戶及授予權限
增加用戶調用存儲過程sp_addlogin
授予權限調用存儲過程sp_grantdbaccess
基本用法為:sp_addlogin username
password sp_grantdbaccess usernamevoid CUser::OnOK()
{
// TODO: Add extra validation here
UpdateData();
CString strDB;
m_database
GetLBText (m_database
GetCurSel()
strDB);
if(m_user
IsEmpty () || m_user==
sa
)
return;
try{
if(m_user_list
FindString (
m_user)<
)
{
cn
>Execute (
sp_addlogin
+(_bstr_t)m_user+
+(_bstr_t)m_user+
NULL
adExecuteNoRecords);
m_user_list
AddString (m_user);
}
else
{
cn
>PutDefaultDatabase ((_bstr_t)strDB);
cn
>Execute (
sp_grantdbaccess
+(_bstr_t)m_user+(_bstr_t)
NULL
adExecuteNoRecords);
m_db_list
AddString (strDB);
}
}
catch(_com_error)
{
AfxMessageBox(
發生錯誤!
);
}
//CDialog::OnOK();
}
刪除用戶及權限: 刪除用戶調用存儲過程sp_droplogin
授予權限調用存儲過程sp_revokedbaccess
基本用法為:sp_droplogin username
sp_revokedbaccess usernamevoid CUser::OnBnClickedDel()
{
UpdateData();
m_user
TrimRight ();
if(m_user
IsEmpty () || m_user==
sa
|| m_user_list
FindString (
m_user)<
)
{
AfxMessageBox(
不能刪除!
);
return;
}
CString strDB;
m_database
GetLBText (m_database
GetCurSel ()
strDB);
try{
if(m_db_list
GetCount ()<
)
{//刪除用戶
cn
>Execute (
exec sp_droplogin
+(_bstr_t)m_user+
NULL
adExecuteNoRecords);
m_user_list
DeleteString (m_user_list
FindString (
m_user));
}
else
{//刪除權限
if(m_db_list
FindString (
strDB)>=
)
{
cn
>PutDefaultDatabase ((_bstr_t)strDB);
cn
>Execute (
exec sp_revokedbaccess
+(_bstr_t)m_user+
NULL
adExecuteNoRecords);
m_db_list
DeleteString (m_db_list
FindString (
strDB));
}
}
}
catch(_com_error)
{
AfxMessageBox(
發生錯誤!
);
}
}
From:http://tw.wingwit.com/Article/program/SQLServer/201311/21990.html