How To Add a User and Set User Privileges to SQL Server

MORE INFORMATION

1. Create a program file called Userconn.prg, using the following code:

         LPARAMETER cDSN,cUserName,cUserPassWord
         *!*   Connect to SQL Server as a restricted access user.
         hConnect=SQLCONNECT(cDSN,cUserName,cUserPassWord)
         IF hConnect >0
            cSQLCommand="SELECT * FROM PUBS.DBO.AUTHORS"
            gnExec = SQLEXEC(hConnect, cSQLCommand,'MYCURSOR')
            ? cSQLCommand,gnExec
            IF gnExec>0
               SELECT mycursor
               BROW
            ENDIF
            *!*   Disconnect as restricted access user.
            =SQLDISCONNECT(hConnect)
         ELSE
            =MESSAGEBOX("Connection failed",0,"Connection Error")
         ENDIF
         RETURN
   								

2. Create a program file called Adduser.prg, using the following code:

         hConnect=SQLCONNECT(cDSN,cUserName,cUserPassWord)
         IF hConnect >0
            lUserExists=.F.
            *!*   Poll MASTER.DBO.SYSLOGINS for the NAME COLUMN.
            cSQLCommand="SELECT NAME FROM MASTER.DBO.SYSLOGINS"
            gnExec = SQLEXEC(hConnect, cSQLCommand,'SYSLOGS')
            IF gnExec>0
               SELECT syslogs
               LOCA
               SCAN FOR ALLTRIM(NAME)=ALLTRIM(cUserName)
                  lUserExists=.T.
                  EXIT
               ENDSCAN
            ENDIF
            IF !lUserExists
               *!*   Select the PUBS database on SQL Server.
               cSQLCommand="USE PUBS"
               gnExec = SQLEXEC(hConnect,cSQLCommand)
               *!*   Add a new SQL Server Login ID cUserName.
               *!*   Access granted to the PUBS database.
               cSQLCommand="EXEC sp_addlogin "+alltrim(cUserName)+"," + ;
                  alltrim(cUserPassWord)+",PUBS"
               gnExec = SQLEXEC(hConnect, cSQLCommand)
               *!*   Grant privileges to user "cUserName".
               cSQLCommand="GRANT SELECT ON pubs.dbo.authors " + ;
                  "TO "+cUserName
               gnExec = SQLEXEC(hConnect, cSQLCommand)
            gnCommit = SQLCOMMIT(hConnect)
            ENDIF
            *!*   Disconnect as System Administrator.
            =SQLDISCONNECT(hConnect)
         ELSE
            =MESSAGEBOX("Connection failed",0,"Connection Error")
         ENDIF
         RETURN
   								

3. From the Command window type the following (MyDsn is equal to a valid ODBC Data Source Name):

         DO USERCONN WITH 'MyDsn','TEST','TEST'
   								

   The following error message appears:
   Connection Failed SQL State: '28000' SQL Server Error: 4002 [Microsoft][ODBC SQL Server Driver][SQL Server] Login Failed
4. In the Command window type the following (MyDsn is equal to a valid ODBC Data Source Name):

         DO ADDUSER WITH 'MyDsn','TEST','TEST'
   								

5. In the Command window type the following:

         DO USERCONN WITH 'MyDsn','TEST','TEST'
   								

   A cursor with data from the PUBS.DBO.AUTHORS table appears in a BROWSE window.