Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

Summary

When using integrated security with SQL Server, the presence or absence of a login ID determines if a user is uniquely known within SQL Server or is instead mapped to a default account, if one exists. If no default account and no login ID exists for a specific user, that user cannot log in.

If no login ID exists for a specific user, the following error message appears when that login ID is used in an attempt to initiate an ODBC connection to SQL Server:
Connection Failed SQL State: '28000' SQL Server Error: 4002 [Microsoft][ODBC SQL Server Driver][SQL Server] Login Failed
This article describes how to add a valid user SQL Server login ID from within Visual FoxPro.

↑ Back to the top


More information

User Login IDs are added to SQL Server with the stored procedure sp_addlogin. Permission to execute sp_addlogin is restricted to the SQL Server System Administrator.

  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.

↑ Back to the top


References

SQL Server 6.5 Help; search on: "sp_addlogin"

(c) Microsoft Corporation 1998, All Rights Reserved. Contributions by John Desch, Microsoft Corporation

↑ Back to the top


Keywords: KB191694, kbsqlprog, kbhowto, kbcode

↑ Back to the top

Article Info
Article ID : 191694
Revision : 5
Created on : 3/3/2005
Published on : 3/3/2005
Exists online : False
Views : 390