What is the preferred method for installing software that requires SQL Server Express? Should you mount the database using a new account created by an admin that installs the software?
If we use the SA account there is no problem at install time, but at runtime there is an error indicating an issue with the LDF file.
Thank you Scott for your excellent question. Ensure that SQLExpress has read, write, modify access to the deployment folder which contains the datafile. Next you need to make sure that all users who access your application may modify the data. You could grant full access, but this is not advised. Instead create a local SQL user account and grant the user the ability to modify your database. Next ensure that the SQL instance has been set up to a mixed mode login By: "USE [master] " & _ "EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2 "
The above will create that log file if the SQL users have the ability to modify the directory!!
Now grant the user created By: "CREATE Login UName WITH PASSWORD = 'UPassword', DEFAULT_DATABASE=[MyDatabase], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF; " & _ "exec sp_addsrvrolemember N'UName', sysadmin; "
Now add that user to all the roles you wish, example:
"USE [MyDatabase] " "EXEC sp_addrolemember N'db_datareader', N'User' "
There are other actions which you may choose to take on the user. But this depends on what your trying to accomplish.
The last step is to connect your application using this new account by:
Connection string: Data Source=*******\SQLEXPRESS;AttachDbFilename=****\Datafile.mdf;Initial Catalog=MyDatabase;Persist Security Info=True;User ID=UNAME;Password=UPassword" />
I should say "The above will create that log file if the SQL users have the ability to modify the directory!!" The act of executing that code as the administrator at run time will create the log file ;)