本文主要通过调用SQL Server的系统存储过程和系统表在程序中动态增加和删除用户
一首先用系统管理员帐号登录系统程序省略请看源程序
二 进入用户管理界面
读取数据库从mastersysdatabse中读取所有数据库
void CUser::AddDataBase()
{
_RecordsetPtr rs;
_bstr_t bt;
HRESULT hr;
CString str;
bt=(_bstr_t)select * from mastersysdatabases;
try{
hr=rsCreateInstance (__uuidof(Recordset));
ASSERT(SUCCEEDED(hr));
//打开数据源
hr=rs>Open (btcnGetInterfacePtr ()adOpenDynamicadLockOptimisticadCmdText);
ASSERT(SUCCEEDED(hr));
rs>MoveFirst();
while(!rs>EndOfFile)
{
str=(char*)(_bstr_t)rs>Fields>GetItem(name)>Value;
m_databaseAddString(str);
rs>MoveNext();
}
rs>Close();
m_databaseSetCurSel();
}
catch(_com_error)
{}
}
读取用户从mastersyslogins视图中读取所有非NT用户void CUser::AddUser()
{
_RecordsetPtr rs;
_bstr_t bt;
HRESULT hr;
CString str;
bt=(_bstr_t)select * from mastersyslogins where isntname=;
try{
hr=rsCreateInstance (__uuidof(Recordset));
ASSERT(SUCCEEDED(hr));
//打开数据源
hr=rs>Open (btcnGetInterfacePtr ()adOpenDynamicadLockOptimisticadCmdText);
ASSERT(SUCCEEDED(hr));
rs>MoveFirst();
while(!rs>EndOfFile)
{
str=(char*)(_bstr_t)rs>Fields>GetItem(name)>Value;
m_user_listAddString(str);
rs>MoveNext();
}
rs>Close();
m_user_listSetCurSel ();
}
catch(_com_error)
{}
}
读取当前用户有权限的数据库每个数据库有权限的用户在每个数据的sysusers表中void CUser::OnSelchangeUserList()
{
CString strUserstrDB;
m_db_listResetContent ();
m_user_listGetText (m_user_listGetCurSel ()strUser);
for(int i=;i<m_databaseGetCount ();i++)
{
m_databaseGetLBText (istrDB);
if(HasRight(strUserstrDB))
{
m_db_listAddString (strDB);
}
}
if(m_db_listGetCount ()>)
{
m_db_listSetCurSel ();
}
}
//查看用户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=rsCreateInstance (__uuidof(Recordset));
ASSERT(SUCCEEDED(hr));
//打开数据源
hr=rs>Open (btcnGetInterfacePtr ()adOpenDynamicadLockOptimisticadCmdText);
ASSERT(SUCCEEDED(hr));
rs>MoveFirst();
rs>Close();
bResult=TRUE;
}
catch(_com_error)
{
}
return bResult;
}
增加用户及授予权限增加用户调用存储过程sp_addlogin授予权限调用存储过程sp_grantdbaccess
基本用法为:sp_addlogin usernamepassword sp_grantdbaccess usernamevoid CUser::OnOK()
{
// TODO: Add extra validation here
UpdateData();
CString strDB;
m_databaseGetLBText (m_databaseGetCurSel()strDB);
if(m_userIsEmpty () || m_user==sa)
return;
try{
if(m_user_listFindString (m_user)<)
{
cn>Execute (sp_addlogin +(_bstr_t)m_user++(_bstr_t)m_user+NULLadExecuteNoRecords);
m_user_listAddString (m_user);
}
else
{
cn>PutDefaultDatabase ((_bstr_t)strDB);
cn>Execute (sp_grantdbaccess +(_bstr_t)m_user+(_bstr_t)NULLadExecuteNoRecords);
m_db_listAddString (strDB);
}
}
catch(_com_error)
{
AfxMessageBox(发生错误！);
}
//CDialog::OnOK();
}
删除用户及权限: 删除用户调用存储过程sp_droplogin授予权限调用存储过程sp_revokedbaccess
基本用法为:sp_droplogin username
sp_revokedbaccess usernamevoid CUser::OnBnClickedDel()
{
UpdateData();
m_userTrimRight ();
if(m_userIsEmpty () || m_user==sa || m_user_listFindString (m_user)<)
{
AfxMessageBox(不能删除!);
return;
}
CString strDB;
m_databaseGetLBText (m_databaseGetCurSel ()strDB);
try{
if(m_db_listGetCount ()<)
{//删除用户
cn>Execute (exec sp_droplogin +(_bstr_t)m_user+NULLadExecuteNoRecords);
m_user_listDeleteString (m_user_listFindString (m_user));
}
else
{//删除权限
if(m_db_listFindString (strDB)>=)
{
cn>PutDefaultDatabase ((_bstr_t)strDB);
cn>Execute (exec sp_revokedbaccess +(_bstr_t)m_user+NULLadExecuteNoRecords);
m_db_listDeleteString (m_db_listFindString (strDB));
}
}
}
catch(_com_error)
{
AfxMessageBox(发生错误!);
}
}
