分类: 数据库开发技术
2008-03-14 12:25:15
一、首先用系统管理员帐号登录系统:程序省略,请看源程序。
二、 进入用户管理界面:
1、 读取数据库:从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(0); } catch(_com_error) {} }2、 读取用户:从master..syslogins视图中读取所有非NT用户。
void CUser::AddUser() { _RecordsetPtr rs; _bstr_t bt; HRESULT hr; CString str; bt=(_bstr_t)"select * from master..syslogins where isntname=0"; 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 (0); } catch(_com_error) {} }3、 读取当前用户有权限的数据库:每个数据库有权限的用户在每个数据的sysusers表中。
void CUser::OnSelchangeUserList() { CString strUser,strDB; m_db_list.ResetContent (); m_user_list.GetText (m_user_list.GetCurSel (),strUser); for(int i=0;i4、 增加用户及授予权限:增加用户调用存储过程sp_addlogin,授予权限调用存储过程sp_grantdbaccess。0) { m_db_list.SetCurSel (0); } } //查看用户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; }
void 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 (0,m_user)<0) { 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(); }5、 删除用户及权限: 删除用户调用存储过程sp_droplogin,授予权限调用存储过程sp_revokedbaccess。
void CUser::OnBnClickedDel() { UpdateData(); m_user.TrimRight (); if(m_user.IsEmpty () || m_user=="sa" || m_user_list.FindString (0,m_user)<0) { AfxMessageBox("不能删除!"); return; } CString strDB; m_database.GetLBText (m_database.GetCurSel (),strDB); try{ if(m_db_list.GetCount ()<1) {//删除用户 cn->Execute ("exec sp_droplogin ''"+(_bstr_t)m_user+"''",NULL,adExecuteNoRecords); m_user_list.DeleteString (m_user_list.FindString (0,m_user)); } else {//删除权限 if(m_db_list.FindString (0,strDB)>=0) { cn->PutDefaultDatabase ((_bstr_t)strDB); cn->Execute ("exec sp_revokedbaccess ''"+(_bstr_t)m_user+"''",NULL,adExecuteNoRecords); m_db_list.DeleteString (m_db_list.FindString (0,strDB)); } } } catch(_com_error) { AfxMessageBox("发生错误!"); } }作者信息: 肖进 南京中萃食品有限公司( 210061)