Add a user in SQL Server using Visual C++

• Download source - 245 Kb

Introduction

In this project, we can add or delete user by using the system procedures and tables. Here we will connect to the database server by ADO, so we must import the file ADODB15.dll first in StdAfx.h.

Before adding or deleting user, we must be a system administrator.

Using the code

First when we logon to the database server, the window of adding/deleting user will be shown after successful login. The codes are not shown here, so you should read the source codes if you want to know the realization.

1. Read the names of all the databases in the database server: use the table sysdatabase of database master.

   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)); 
           //the pointer of recordset must be valid
           //open the recordset
           hr=rs->Open (bt,cn.GetInterfacePtr (),
             adOpenDynamic,adLockOptimistic,adCmdText);
           ASSERT(SUCCEEDED(hr));  
           rs->MoveFirst();  //move to the first record
           while(!rs->EndOfFile)
           {   //read the name of databse
               str=(char*)(_bstr_t)rs->Fields->GetItem("name")->Value;
               m_database.AddString(str);  
               rs->MoveNext();
           }
           rs->Close();  //close the recordset
           m_database.SetCurSel(0);
       }
       catch(_com_error)
       {}  //if error then do nothing
   }

2. Read the name of all the users except NT domain users: use the view syslogins of database master.

   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));
           //open recordset
           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)
       {}//if error then do nothing
   }

3. Check all the databases if the user has the privilege to access them. If the user can access the database, then his name will be added into the database listbox. When the line number of user listbox has changed, the corresponding database name in right listbox will be changed too. If the table sysusers in the database has record of the user, then it means the user has privilege to access the database.

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

   Check if the user has privilege to access the 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));
           //open the recordset
           hr=rs->Open (bt,cn.GetInterfacePtr (),adOpenDynamic,
             adLockOptimistic,adCmdText);
           ASSERT(SUCCEEDED(hr));
           rs->MoveFirst();
           rs->Close();
           bResult=TRUE;
       }
       catch(_com_error)
       {
       }
       return bResult;
   }

4. Add new user and grant access privilege to the user use the system procedure sp_addlogin to add user, and use system procedure sp_grantdbaccess to grant privilege to the user.

   Basic usage:

   • sp_addlogin username,password
   • sp_grantdbaccess username

   void CUser::OnOK()
   {
       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("Error！");
       }
   }

5. Delete existing user and privilege on the relational database: we use the system procedure sp_droplogin to delete user, and use the system procedure sp_revokedbaccess to delete privilege on the database.

   Basic usage:

   • sp_droplogin username
   • sp_revokedbaccess username

   void CUser::OnBnClickedDel()
   {
       UpdateData();
       m_user.TrimRight ();
       if(m_user.IsEmpty () || m_user=="sa" || 
           m_user_list.FindString (0,m_user)<0)
       {
           AfxMessageBox("Can’t Delete it or empty!");
           return;
       }
   
       CString strDB;
       m_database.GetLBText (m_database.GetCurSel (),strDB);
       try{
           if(m_db_list.GetCount ()<1)
           {//delete user
               cn->Execute ("exec sp_droplogin '"+(_bstr_t)m_user+
                   "'",NULL,adExecuteNoRecords);
               m_user_list.DeleteString (
                   m_user_list.FindString (0,m_user));
           }
           else
           {//delete access priviliage
               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("ERROR!");
       }
   }

That's all!