December 23, 2015 at 6:02 am
Hi all
I'm designing a database front end to help us with user administration (and it looks better than the SSMS GUI).
I've got most of it working but run across a bit of an oddity.
This is the code that creates the initial logon (we use "SQL Server and Windows Authentication mode" on the servers).:-
Private Sub create_user(add_user)
'----- Create SQL for creating new user -----
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]"
' On Error GoTo 0
'----- Create new user on server -----
Set qdf = Nothing
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")
Set qdf = Nothing
End Sub
and it works on all servers.
A similar piece of code adds users to databases with appropriate permissions and this is it:-
Private Sub update_permissions(add_user)
'----- 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 = "create user [" & add_user & "] for login [" & add_user & _
"] with default_schema=[dbo]"
'----- Create new user on on database -----
Set qdf = Nothing
Set qdf = CurrentDb.CreateQueryDef("")
qdf.Connect = "ODBC;DRIVER=SQL Server;SERVER=" & server & ";DATABASE=" & db & ";"
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 = "use " & db & " exec sp_addrolemember N'" & v_chkbox.Name & _
"', N'" & add_user & "'"
Else
sSQL = "use " & db & " exec sp_droprolemember N'" & v_chkbox.Name & _
"', N'" & add_user & "'"
End If
Set qdf = Nothing
Set qdf = CurrentDb.CreateQueryDef("")
qdf.Connect = "ODBC;DRIVER=SQL Server;SERVER=" & server & ";DATABASE=" & db & ";"
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")
Set qdf = Nothing
End Sub
This works as well.
However, when it comes to getting rid of users I get an "ODBC call failed" error. The code is here:-
Private Sub Remove_User(Delete_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 Resume Next
response = MsgBox("Are you sure you want to delete this user?", 20, "XXX WARNING XXX")
If response = vbNo Then Exit Sub
sSQL = "SELECT Database FROM Database_list where Server='" & server & "';"
Set rs = CurrentDb.OpenRecordset(sSQL)
rs.MoveFirst
Do While Not rs.EOF
sSQL = "use [" & rs(0) & "] drop user [" & Delete_User & "] "
Set qdf = Nothing
Set qdf = CurrentDb.CreateQueryDef("")
qdf.Connect = "ODBC;DRIVER=SQL Server;SERVER=" & server & ";DATABASE=Master"
qdf.SQL = sSQL
qdf.ReturnsRecords = False
qdf.Execute
rs.MoveNext
Loop
On Error GoTo 0
sSQL = "drop login [" & Delete_User & "]"
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
On Error GoTo 0
response = MsgBox("User deleted successfully", vbOKOnly, "SUCCESS")
Set qdf = Nothing
End Sub
The last piece of code works on a 2012 server, but not on 2008 or 2005 servers.
Anyone any idea why?
I've checked the syntax of the drop user command for each database and it works in SSMS (on all servers).
The "On Error Resume Next" line is normally in place as not all users will have access to all databases and that is there so the code doesn't crash.
I've checked that the user does have permissions in the relevant database but the code still crashes.
Any help on this would be greatly appreciated.
December 23, 2015 at 10:59 am
Interesting idea - admin of users in multiple databases using SSMS can be a pain.
Haven't seen any differences in doing user admin between 2008 and 2012, but that doesn't mean there aren't issues. Do you know which instruction the error is occurring? It could be in several places, so adding line numbers to your code would be a good idea.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
January 4, 2016 at 6:29 am
This is very bizarre.
I've just retested my code against all servers (2005/2008/2012) using a test login and it's all working fine.
I've created the user, adjusted the permissions and then deleted my test user and it's all working according to plan.
I've now got a fully-working database front end to manage users across multiple servers and it's only 732KB (Access 2010). 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply