This code results in "Syntax error in CREATE TABLE statement", even though I'm not using a CREATE TABLE.
A print statement generates this:
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: CREATE TABLE Statement (Microsoft Access SQL) 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.
If the destination of the SQL statement is a SQL Server, you need to use a pass-through query, MS-Access does not recognize CREATE LOGIN as a valid SQL statement. And yes: you should not try to manipulate the security objects and features of a SQL server in an Access application.