Click here to Skip to main content
Click here to Skip to main content

Add a user in SQL Server using Visual C++

, 2 Aug 2003
Rate this:
Please Sign up or sign in to vote.
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

Share

About the Author

xiaojin

China China
No Biography provided

Comments and Discussions

 
GeneralMy vote of 3 PingroupMahdi Nejadsahebi23-Oct-11 1:47 
GeneralAn alternative approach Pinmemberapferreira4-Aug-03 6:23 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140814.1 | Last Updated 3 Aug 2003
Article Copyright 2003 by xiaojin
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid