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 Administer SQL Server Users from Visual FoxPro


View products that this article applies to.

Summary

Using stored procedures on a SQL Server and the Visual FoxPro SQLEXEC() function, it is possible to administer database users and groups from within a Visual FoxPro program. The following sample code demonstrates how to accomplish this.

↑ Back to the top


More information

The sample code assumes that an ODBC datasource has been created to connect to the master database. The datasource should connect to the database that contains the user you wish to work with. So, if you want to administrate a user in the "pubs" database, change the ODBC connection so that it connects to the "pubs" database.

If users are being dropped from a database other than master, the program needs to explicitly call the necessary stored procedures from the master database: "master.dbo.sp_adduser." This convention is used in the sample code for greater clarity, though it is not needed since the master database is being used in the sample code.

The program user needs to have administrator privileges to perform these actions.
   *-- Code begins here
   *-- Change the datasource, user name and password as appropriate.
   gnConnect = SQLCONNECT("moonbase", "sa", "")
   IF gnConnect <= 0
       =MESSAGEBOX("Could not establish a database connection.", ;
         0, "Connection Error")
       RETURN
   ENDIF

   *-- Add user id "mike" if is not already exist in the master database.
   gnLogin = SQLEXEC(gnConnect, 'exec master.dbo.sp_addlogin "mike", ""')
   IF gnLogin > 0
      WAIT WINDOW "User Login added"
   ELSE
      WAIT WINDOW "User Login already exist"
   ENDIF

   *-- Add a user "mike" and put it in the "public" group.
   gnAddUser = SQLEXEC(gnConnect, 'exec master.dbo.sp_adduser "mike", ;
     "mike", "public"')
   IF gnAddUser > 0
      WAIT WINDOW "User was successfully added."
   ELSE
      WAIT WINDOW "Adding user failed."
   ENDIF

   *-- Add a test group
   gnAddGroup = SQLEXEC(gnConnect, 'exec master.dbo.sp_addgroup "test"')
   IF gnAddGroup > 0
      WAIT WINDOW "Group was successfully added."
   ELSE
      WAIT WINDOW "Adding group failed."
   ENDIF

   IF gnAddUser > 0 AND gnAddGroup > 0
      *-- Add the user to the test group
      gnAddUserGroup = SQLEXEC(gnConnect, ;
          'exec master.dbo.sp_changegroup "test", "mike"')
      IF gnAddUserGroup > 0
         WAIT WINDOW "User was successfully added to group."
      ENDIF
   ENDIF

   *-- Now drop the user.
   IF gnAddUser > 0
      gnDropUser = SQLEXEC(gnConnect, 'exec master.dbo.sp_dropuser "mike"')
      IF gnDropUser > 0
         WAIT WINDOW "User was successfully dropped."
      ENDIF
   ENDIF

   *-- And drop the group
   IF gnAddGroup > 0
      gnDropGroup = SQLEXEC(gnConnect, ;
          'exec master.dbo.sp_dropgroup "test"')
   ENDIF
   IF gnDropGroup > 0
      WAIT WINDOW "Group was successfully dropped."
   ENDIF
   SQLDISCONNECT(gnConnect)
   *--Code ends here
				

↑ Back to the top


References

Transact-SQL Reference Help; search on: sp_adduser, sp_dropuser, sp_addgroup, sp_dropgroup

(c) Microsoft Corporation 1998, All Rights Reserved. Contributions by Mike Stewart, Microsoft Corporation

↑ Back to the top


Keywords: KB189918, kbsqlprog, kbhowto

↑ Back to the top

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