I have just received a rather nice piece of code from Metra Christofferson at SQLServerCentral. You can can click on the link here to see the complete thread. (
http://www.sqlservercentral.com/Forums/FindPost560449.aspx )
I am copying his code here - after a little bit of cleaning up for readability's sake and then I will attempt to document it later.
I am also including it into the SQL Admin.ACCDB file that I will upload again once I have it working. I have highlighted the bits that are important to me. It would appear that you can construct any T-SQL statement and then pump it through DAO - which for me is a long way towards the solution I am looking for.
Code courtesy of Metra Christofferson
'=========Start here ==========
Public Function CreateNewSQLUser(NewUserName As String, SQLdbName As String)
On Error GoTo PROC_ERR
Dim bsSQL As String
bsSQL = "USE [master] " _
& "if not exists (select * from master.dbo.syslogins where loginname = N'" & NewUserName & "')" _
& " BEGIN " _
& " declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'master', @loginlang = N'us_english'" _
& " if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)" _
& " select @logindb = N'master'" _
& " if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')" _
& " select @loginlang = @@language" _
& " exec sp_addlogin N'" & NewUserName & "', null, @logindb, @loginlang" _
& " exec sp_grantlogin N'" & NewUserName & "'" _
& " exec sp_defaultdb N'" & NewUserName & "', N'master'" _
& " exec sp_defaultlanguage N'" & NewUserName & "', N'us_english'" _
& " END " _
& " USE [master] " _
& " exec sp_grantdbaccess '" & NewUserName & "', '" & Right(NewUserName, Len(NewUserName) - InStr(1, NewUserName, "\")) & "'" _
& " USE [msdb] " _
& " exec sp_grantdbaccess '" & NewUserName & "', '" & Right(NewUserName, Len(NewUserName) - InStr(1, NewUserName, "\")) & "'" _
& " USE [tempdb] " _
& "exec sp_grantdbaccess '" & NewUserName & "', '" & Right(NewUserName, Len(NewUserName) - InStr(1, NewUserName, "\")) & "'" _
& " USE [" & SQLdbName & "] " _
& "exec sp_grantdbaccess '" & NewUserName & "', '" & Right(NewUserName, Len(NewUserName) - InStr(1, NewUserName, "\")) & "'"
Dim qdef As DAO.QueryDef
Set qdef = CurrentDb.QueryDefs("qryTempPassthrough")
qdef.Connect = "ODBC;" & GetConnString()
qdef.ODBCTimeout = 120
qdef.SQL = bsSQL
qdef.Close
Set qdef = Nothing
DoCmd.OpenQuery ("qryTempPassthrough")
PROC_EXIT:
DoCmd.SetWarnings True
Exit Function
PROC_ERR:
If Err.Number = 3325 Or Err.Number = 3146 Or Err.Number = 3270 Then Resume PROC_EXIT
If Err.Number = 3390 Then
MsgBox "This User Name already exists."
DoCmd.Close acForm, "frmAddEdd_NewUser", acSaveNo
Resume PROC_EXIT
Else
MsgBox Err.Number & ", " & Err.Description & " on basSecure in CreateNewUser ", , AppErrTitle
End If
Resume PROC_EXIT
Resume
End Function