Code:
NewLogin = "CREATE LOGIN " & Me.txtUserID & " WITH PASSWORD = '" & Me.txtPassword & "'; USE myDB;" _
& "CREATE USER " & Me.txtUserID & " FOR LOGIN " & Me.txtUserID & "; EXEC sp_addrolemember 'myDB_admin', '" & Me.txtUserID & "'"
dbs.Execute NewLogin
This code results in "Syntax error in CREATE TABLE statement", even though I'm not using a CREATE TABLE.
A print statement generates this:
Code:
CREATE LOGIN admin2 WITH PASSWORD = 'password'; USE myDB;CREATE USER admin2 FOR LOGIN admin2; EXEC sp_addrolemember 'myDB_admin', 'admin2'
Which, according to my SQL Server, is valid SQL code. When run as a query server side it pops up the login, user, and role just fine.
According to this note here: http://msdn.microsoft.com/en-us/library/ff837200.aspx MS Access doesn't directly support this form of CREATE statements though? It says refer to the DAO Create, but that page isn't filled out on MSDN. Therefore I'm a little confused as to what I need to do.
I'm trying to create SQL server logins through this Access DB. I know that is horribly, horribly insecure, and probably why it won't allow me to do it. Is there any way around this? Is there a different method of doing this that is preferred? Should I be smacked in the head for even trying this? Keep in mind I still don't know much about SQL Server, but definitely enough to get me in trouble.