Microsoft KB Archive/191694

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

Article ID: 191694

Article Last Modified on 3/3/2005

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 3.0b Standard Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft Visual FoxPro 3.0b for Macintosh
 * Microsoft Data Access Components 2.5

-



This article was previously published under Q191694



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.



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.

 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

 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

 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

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

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

 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.

