Controlling SQL users

[RichardMGreen]

Hi all

I'm designing a database fromnt-end to give us (easier) control over users on various SQL servers.
I've got the create user and delete user section working (everything is based on Windows logons) but I'm struggling with the bit on assigning permissions to databases.

This code works:-

Private Sub create_user(add_user)
    Stop
'----- Create SQL for creating new user -----
    Form_Load_Menu.Database_Name.SetFocus
    db = Form_Load_Menu.Database_Name.Text
    Form_Load_Menu.Server_Name.SetFocus
    server = Form_Load_Menu.Server_Name.Text
    On Error GoTo dropout
    sSQL = "CREATE LOGIN [" & add_user & "] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[British]"
'----- Create new user on server -----
    Set qdf = CurrentDb.CreateQueryDef("")
    qdf.Connect = "ODBC;DRIVER=SQL Server;SERVER=" & server & ";DATABASE=Master"
    qdf.SQL = sSQL
    qdf.ReturnsRecords = False
    qdf.Execute
    Set qdf = Nothing
    Form_Load_Menu.newuser = False
    response = MsgBox("User created successfully", vbOKOnly, "SUCCESS")
Exit Sub
dropout:
'----- Error message if user already exists -----
    Err.Clear
    response = MsgBox("User already exists" & vbCrLf & "Please assign permissions for this user", vbOKOnly, "XXX WARNING XXX")
End Sub

This bit doesn't:-

Private Sub update_permissions(add_user)
    Stop
'----- Loop through to update permissions -----
'----- Assign User to database with default schema of DBO but only if it's a new user -----
    Form_Load_Menu.Database_Name.SetFocus
    db = Form_Load_Menu.Database_Name.Text
    Form_Load_Menu.Server_Name.SetFocus
    server = Form_Load_Menu.Server_Name.Text
'    On Error GoTo set_permissions
    sSQL = "use [" & db & "] go create user [" & add_user & "] for login [" & add_user & _
        "] with default_schema=[dbo]"
'----- Create new user on on database -----
    Set qdf = CurrentDb.CreateQueryDef("")
    qdf.Connect = "ODBC;DRIVER=SQL Server;SERVER=" & server & ";DATABASE=Master"
    qdf.SQL = sSQL
    qdf.ReturnsRecords = False
    qdf.Execute
    Set qdf = Nothing
set_permissions:
'----- Loop through check boxes and set permissions if necessary -----
    If Form_Load_Menu.Database_Name <> "" Then
        For Each v_chkbox In Form_Load_Menu.Controls
            If v_chkbox.ControlType = acCheckBox And v_chkbox.Name <> "newuser" And v_chkbox.Name Like "db*" Then
'----- Set permissions based on the selections made and database chosen -----
                If v_chkbox Then
                    sSQL = "exec [" & db & "].dbo.usp_add_user_role @role_name='" & v_chkbox.Name & _
                        "', @username='" & add_user & "'"
                Else
                    sSQL = "exec [" & db & "].dbo.usp_drop_user_role @role_name='" & v_chkbox.Name & _
                        "', @username='" & add_user & "'"
                End If
                Set qdf = CurrentDb.CreateQueryDef("")
                qdf.Connect = "ODBC;DRIVER=SQL Server;SERVER=" & server & ";DATABASE=Master"
                qdf.SQL = sSQL
                qdf.ReturnsRecords = False
                qdf.Execute
                Set qdf = Nothing
            End If
        Next v_chkbox
    End If
    response = MsgBox("User updated successfully", vbOKOnly, "SUCCESS")
End Sub

The SQL syntax is accurate (and works if I paste it directly into SSMS) but the code fails on the first qdf.Execute with an "ODBC call fails" error. This section should attach the user to the database and then assign permissions based on a series of checkboxes on the userform.

Can anyone see any issues with what I've done?

I've had this working previously using ODBC connections but I'm trying to make it more generic so you can choose the server to start with. I'm also trying to do away with ODBC connection stored in the registry as there's no guarantee that everyone will call them the same (or even have the permissions to create them).

 

[mole999]

we use a simple form, that gets sent to the server admin, who then grants access based on the managers agreement, for the environment that the user needs. It maybe dumb, but it requires human intervention to protect the network

 

[james_lankford]

don't know if it will make a difference, but try putting a new line before and after the go

sSQL = "use [" & db & "] " & vbCrLf & "go" & vbCrLf & "create user [" & add_user & "] for login [" & add_user "] with default_schema=[dbo]"

and nobody but you is going to have access to this application, correct ?

because you're just asking for trouble with all that sql and access to the server

have you thought about making a stored procedure
and then just passing the parameters ?

 

[RichardMGreen]

mole999 - we (the business) don't have any SQL admins as such, we (the team I work in) just get requests and then have to do things the long way round in SSMS.

james_lankford - I'll try that, thanks. There will be others in the team I work in that will have access to this application but they have the same admin rights as I do anyway.

 

[xenou]

I'd use a pass through query rather than ODBC - might get better results.

Note: as a general rule, also always output your sql text and test directly in sql server. Don't assume ODBC can handle all SQL Server scripts the same as SSMS. There are some things ODBC cannot do.