February 9, 2006 at 2:01 pm
I'm trying to build a program in VB6 which will 'implant' a database into a SQL 2000 server for a program that I'm writing, so that I don't have to build the database by hand every time I distribute the program. It's a kind of setup program, and I don't want to use VB's packaging wizard because it doesn't work all that well for me. I was able to map out all the commands I needed to pass to the server through good 'ol T-SQL commands. When I do it manually through the command prompt (OSQL.EXE), everything works out just fine. But when I try to do the same thing with SQL-DMO's ExecuteImmediate functions, everything except adding a login to the database that's being created works. When I create a login for the database, it puts the login on the Master database. It shouldn't go there. Here's what I have.
(oServer is a SQLDMO.SQLServer2 object. Assume the database has been created and is valid, the connection is good with a proper server name, using sa user/password, just for the purposes of creating this login)
oServer.ExecuteImmediate "USE myProgsDB"
oServer.ExecuteImmediate "GO"
oServer.ExecuteImmediate "EXEC sp_addlogin 'NewLogin', 'password', myProgsDB"
oServer.ExecuteImmediate "GO"
The problem is that it's not switching over to the correct database, and the new login is being created in the Master database. I know it's not switching because I created a Role in the new database, and when I try to add the user to that Role, it claims that the Role doesn't exist. This little script works in OSQL perfectly. Why isn't it working here?
Or is there a better way to create a login for a particular database, and add that user to the Role for that database?
February 9, 2006 at 2:18 pm
Nevermind. I answered my own question yet again.
I completely forgot about the SQLServer.Databases object.
For anyone who's interested...
oServer.Databases("myProgsDB").ExecuteImmediate "EXEC sp_addlogin 'NewLogin', 'password', myProgsDB"
oServer.ExecuteImmediate "GO"
That does it perfectly.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply