user.cpp

VC环境下实现动态添加删除用户

// User.cpp : implementation file
//

#include "stdafx.h"
#include "SQL.h"
#include "User.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif

/////////////////////////////////////////////////////////////////////////////
// CUser dialog


CUser::CUser(CWnd* pParent /*=NULL*/)
: CDialog(CUser::IDD, pParent)
{
	//{{AFX_DATA_INIT(CUser)
	m_user = _T("");
	//}}AFX_DATA_INIT
}


void CUser::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CUser)
	DDX_Control(pDX, IDC_USER_LIST, m_user_list);
	DDX_Control(pDX, IDC_DB_LIST, m_db_list);
	DDX_Control(pDX, IDC_DATABASE, m_database);
	DDX_Text(pDX, IDC_USER, m_user);
	//}}AFX_DATA_MAP
}


BEGIN_MESSAGE_MAP(CUser, CDialog)
//{{AFX_MSG_MAP(CUser)
ON_LBN_SELCHANGE(IDC_USER_LIST, OnSelchangeUserList)
//}}AFX_MSG_MAP
ON_BN_CLICKED(IDOK, OnBnClickedOk)
ON_BN_CLICKED(IDC_DEL, OnBnClickedDel)
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CUser message handlers

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)
	{
	}
}

BOOL CUser::OnInitDialog() 
{
	CDialog::OnInitDialog();
	
	// TODO: Add extra initialization here
	AddDataBase();
	AddUser();
	OnSelchangeUserList();
	return TRUE;  // return TRUE unless you set the focus to a control
	// EXCEPTION: OCX Property Pages should return FALSE
}

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)
	{
	}
}

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();
}

void CUser::OnSelchangeUserList() 
{
	// TODO: Add your control notification handler code here
	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);
	}
}

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::OnBnClickedOk()
{
	// TODO: 在此添加控件通知处理程序代码
	OnOK();
}

void CUser::OnBnClickedDel()
{
	// TODO: 在此添加控件通知处理程序代码
	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)
	{//del the user
      cn->Execute ("exec sp_droplogin '"+(_bstr_t)m_user+"'",NULL,adExecuteNoRecords);
	  m_user_list.DeleteString (m_user_list.FindString (0,m_user));

	}
	else
	{//del the right of this database
      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("发生错误!");
	}

}