VB and VBA Users Source Code: Adding a user to an SQL server database
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Adding a user to an SQL server database
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Tuesday, September 25, 2001
Hits:
827
Category:
Database/SQL/ADO
Article:
Below is a simple routine which can be used to add users to a database. Note, you will need to create the connection which the appropriate rights to allow creation of a new user (eg. SA). Option Explicit 'Purpose : This function adds a new user to an SQL server database. 'Inputs : UserName The name of the user to add. ' Password The users password. ' oConn An open connection to the database. ' [Database] If specified will add user to this database, ' rather than using the default database from the connection. 'Outputs : Returns True if successful, false if otherwise. 'Author : Andrew Baker 'Date : 19/Sep/2001 15:50 'Notes : Adds a User with the Specified Password to the Public Group ' of a database. ' Requires a reference to ADO 2.1 or greater ' You will require the relevant permisssions to add a user (i.e System Administrator) Public Function SQLServerAddUser(UserName As String, Password As String, oConn As ADODB.Connection, Optional Database As String) As Boolean On Error GoTo ErrFailed 'Add user to database oConn.Execute "USE Master" If Len(Database) Then 'Use the same database as the connection oConn.Execute "EXEC sp_addlogin " & UserName & "," & Password & "," & Database oConn.Execute "USE " & Database Else 'Use a different database to the connection oConn.Execute "EXEC sp_addlogin " & UserName & "," & Password & "," & oConn.DefaultDatabase oConn.Execute "USE " & oConn.DefaultDatabase End If 'Grant user access to Database in public group oConn.Execute "EXEC sp_adduser " & UserName SQLServerAddUser = True Exit Function ErrFailed: Debug.Print "Error in SQLServerAddUser: " & Err.Description SQLServerAddUser = False End Function 'Demonstration routine Sub Test() Dim sConString As String Dim oConn As ADODB.Connection sConString = "Provider=SQLOLEDB.1;" sConString = sConString & "User ID=sa;password=mypassword;" sConString = sConString & "Initial Catalog=MyDatabase;" sConString = sConString & "Data Source=MySQLServer;" oConn.Open sConString SQLServerAddUser "TestUser", "TestPassword", oConn End Sub
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
