Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
(untagged)

Add a user in SQL Server using Visual C++

0.00/5 (No votes)
2 Aug 2003 1  
The article explains adding a user in SQL Server programmatically, using VC++

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&#65281;");
        }
    }
  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!

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here