send CREATE LOGIN to SQL Server via VBA?

[madEG]

Hello,


If I could wave a magic wand...?


I'd like to use VBA to send back CREATE LOGIN statements for a new domain sql server user and add db_datawriter & _reader to this new user.


I found this link here, and I think this got me pushed in the right direction, even though it's for azure:

http://gainingaccess.net/Articles/CreateSQLAzureDBUsers.aspx

I am using a local sql server on site. I'm logged into windows domain account, and the ms access app calling this is connected to the sql server via ODBC linked tables also via Active Directory username, which is also with my sql server admin windows credentials.



The code I dug up above, which I tried to modify for my use is:

Private Sub btmCreateSQLServerUser_Click()
'Public Function CreateSQLAlexServerDBUser(strLoginName As String) As Boolean
    On Error GoTo ErrHandle

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String

    CreateSQLAlexServerDBUser = False 'Default Value

    strSQL = "USE MASTER " & _
         "GO " & _
         "CREATE LOGIN " & "[usccb\" & txtUserLogin & "] FROM WINDOWS WITH DEFAULT_DATABASE=[PrioPlansDB5], DEFAULT_LANGUAGE=[us_english] " & _
         "GO " & _
         "USE [PrioPlansDB5] " & _
         "EXEC sp_addrolemember @rolename = 'db_datareader', @membername = [usccb\" & txtUserLogin & "] " & _
         "EXEC sp_addrolemember @rolename = 'db_datawriter', @membername = [usccb\" & txtUserLogin & "] " & _
         "GRANT CONNECT TO [usccb\" & txtUserLogin & "] "

    MsgBox ("strSQL: " & strSQL)

    'Create the Database User
    Set db = CurrentDb

    Set qdf = db.CreateQueryDef("")
    'Change obfuscatedFunctionName to the name of a Function
    'that Returns your SQL Azure Database Connection String
    'qdf.Connect = obfuscatedFunctionName
    qdf.SQL = strSQL
    qdf.ReturnsRecords = False
    qdf.Execute dbFailOnError

    'If no errors the Database User was Created
    CreateSQLAlexServerDBUser = True

ExitHere:
    'Cleanup for security and to release memory
    On Error Resume Next
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub


ErrHandle:
    MsgBox "Error " & Err.Number & vbCrLf & Err.Description _
    & vbCrLf & "In procedure CreateSQLAlexServerDBUser"
    Resume ExitHere

End Sub

...but when I run it I get the error 3192, Invalid SQL Statements, expected DELETE, INSERT, Procedure, select or update...

I've never tried something like this before. I'm not sure if this approach is going to work, but I'm hopeful

Can someone give me a push in the right direction?

Thanks!
-Matt G.






edit: So, in short, I'd like to send this t-SQL (or comparable) below, to the backend to create a new user, from my ms access application...

USE [master]
GO

CREATE LOGIN [domain\username] FROM WINDOWS WITH DEFAULT_DATABASE=[DBname], DEFAULT_LANGUAGE=[us_english]
GO

USE [DBname]  

EXEC sp_addrolemember @rolename = 'db_datareader', @membername = [domain\username]
EXEC sp_addrolemember @rolename = 'db_datawriter', @membername = [domain\username]

GRANT CONNECT  TO [domain\username]

 

[sonic8]

    Set qdf = db.CreateQueryDef("")
    'Change obfuscatedFunctionName to the name of a Function
    'that Returns your SQL Azure Database Connection String
    'qdf.Connect = obfuscatedFunctionName
    qdf.SQL = strSQL

...but when I run it I get the error 3192, Invalid SQL Statements, expected DELETE, INSERT, Procedure, select or update...

The query is not connected to SQL Server but executed locally. qdf.Connect needs to be assigned.