DOCUMENT:Q183621  07-APR-1998  [vbwin]
TITLE   :HOWTO: Configure SQL Server Security via RDO and ADO
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:WINDOWS:5.0
OPER/SYS:WINDOWS
KEYWORDS:vb5all vb5howto

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual Basic Enterprise Edition for Windows, version 5.0
---------------------------------------------------------------------

SUMMARY
=======

This article describes how to use RDO and ADO via Visual Basic to
manipulate SQL Server security settings for a SQL Server that is configured
with standard security (as opposed to the integrated or mixed security
models). Specifically, this article demonstrates how to add a login to a
SQL Server, how to add a user to a SQL Server database, how to add a group
to a SQL Server database, and how to add a user to a group. Other security-
related functions (such as dropping a user from a database and dropping a
login from an SQL Server) can be accomplished by applying similar
techniques to those discussed below.

It is assumed that the person connecting to SQL Server to manipulate the
security settings is either the system administrator (SA) or has a login
that is aliased to the SA.

MORE INFORMATION
================

The following code samples demonstrate how to connect to a SQL Server
through RDO, how to add a login to the SQL Server, how to assign that login
to the Pubs database as a user, how to add a group to the Pubs database,
and, finally, how to add the user to that group. This is demonstrated using
both RDO and ADO data access methods. To run this code you will need to
create a new Visual Basic Standard EXE project and add a reference to
either Microsoft Remote Data Object 2.0 (RDO) or Microsoft ActiveX Data
Objects 1.5 (ADO). Also, to make this code work, you must substitute the
name of your server, a userID, and a password into the ConString string.

Note the use of the statement "On Error Resume Next." This is included to
prevent an error from occurring if you attempt to create duplicate logins,
users, or groups.

Using RDO
---------

   Dim Con as rdoConnection
   Dim ConString As String

   On Error Resume Next

   'establish the connection to the database

   ConString = "UID=<your userID>;PWD=<your password>;DATABASE=Pubs;"
   ConString = ConString & "SERVER=<your server name>;"
   ConString = ConString & "DRIVER={SQL Server};DSN='';"

   Set Con = rdoEnvironments(0).OpenConnection(dsname:="", _
      prompt:=rdDriverNoPrompt, connect:=ConString)

   'make the Master database the current database

   Con.Execute "use Master"

   'add a login called "TestLogin", which has a password equal to
   '"password"; Pubs is specified as the default database for this login

   Con.Execute "exec sp_addlogin TestLogin, password, Pubs"

   'make Pubs the current database and add TestLogin as a user to Pubs
   'by default, TestLogin will be added to the Public group

   Con.Execute "use Pubs"
   Con.Execute "exec sp_adduser TestLogin"

   'now add a group called TestGroup to Pubs and add the TestLogin user to
   'the TestGroup group

   Con.Execute "exec sp_addgroup TestGroup"
   Con.Execute "exec sp_changegroup TestGroup, TestLogin"

   Con.Close

Using ADO
---------

   Dim Con As New ADODB.Connection
   Dim ConString As String

   On Error Resume Next

   ConString = "UID=<your userID>;PWD=<your password>;DATABASE=Pubs;"
   ConString = ConString & "SERVER=<your server name>;"
   ConString = ConString & "DRIVER={SQL Server};DSN='';"

   With Con
      .Open ConString
      .Execute "use Master"

      'add a login called "TestLogin," which has a password equal to
      '"password"; Pubs is specified as the default database for this login

      .Execute "exec sp_addlogin TestLogin, password, Pubs"

      'make Pubs the current database and add TestLogin as a user to Pubs
      'by default, TestLogin will be added to the Public group

      .Execute "use Pubs"
      .Execute "exec sp_adduser TestLogin"

      'now add a group called TestGroup to Pubs and add the TestLogin user
      'to the TestGroup group

      .Execute "exec sp_addgroup TestGroup"
      .Execute "exec sp_changegroup TestGroup, TestLogin"

      .close

   End With

Additional query words: SQLserver
======================================================================
Keywords          : vb5all vb5howto
Technology        : kbado kbrdo
Version           : WINDOWS:5.0
Platform          : WINDOWS
Issue type        : kbhowto

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1998.