本文主要通过调用SQL Server7的系统存储过程和系统表，在程序中动态增加和删除用户。
一、首先用系统管理员帐号登录系统：程序省略，请看源程序。
二、 进入用户管理界面：
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;i<m_database.GetCount ();i++)
{
m_database.GetLBText (i,strDB);
if(HasRight(strUser,strDB))
{
m_db_list.AddString (strDB);
}
}
if(m_db_list.GetCount ()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;
}
4、 增加用户及授予权限：增加用户调用存储过程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 (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。
基本用法为:sp_droplogin username
sp_revokedbaccess usernamevoid 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("发生错误!");
}
}