Click here to Skip to main content
15,867,453 members
Articles / Desktop Programming / MFC
Article

Add a user in SQL Server using Visual C++

Rate me:
Please Sign up or sign in to vote.
4.14/5 (11 votes)
2 Aug 20031 min read 67.5K   2.1K   34   2
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!");
        }
    }
  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


Written By
China China
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 3 Pin
Mahdi Nejadsahebi23-Oct-11 1:47
Mahdi Nejadsahebi23-Oct-11 1:47 
GeneralAn alternative approach Pin
apferreira4-Aug-03 6:23
apferreira4-Aug-03 6:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.