Working with Microsoft SQL Server 2008 and Symfony without sa Login

On a previous post Running Symfony 1.4 with MS SQL Server 2008 I described how to connect to MS SQL Server 2008 using Symfony 1.4.8.
I did all my tests using the sa (superadmin) login and SQL Server Express 2008 R2. I figured once I had my model stable enough all I would have to do would be create a new login and work with it. As usual I was sorely mistaken. The problem is that to operate on a database you need to create a user (in the database) to give it permissions to do things like create table or create data. When you drop the database (for example while running a symfony doctrine:build --all) you eliminate the user, and the login resets to the dbo user. After the database is recreated there is no user associate to the current login and you can’t do anything.
Working with sa on a development environment is not a very good idea, but using it in a production environment is even worst. I found a work around not to use sa as login, not a very elegant one, but it works. The permissions and roles I’am giving the login and user are probably overkill but I need to make this work sooner than later so if you´re going to a production environment I would suggest to take a closer look a the permissions.

Creating the Database

Open Microsoft SQL Server Management Studio and connect to the Instance using sa or other administrative login.
Right click on Databases and select New Database... Create a new database called sfex_db and choose as owner a default for now.

Creating an Administrative Login

Create a Login. Right click on MYMachine\SQLEXPRESS\Security\Logins select New Login. Use as Login name sfex_salogin, assign a password and select as Default database sfex_db.

On Server Roles add the following:



Select the User Mappings page select sfex_db change the user to sfex_sauser and leave the default schema blank. I tried creating a schema an assigning it as the default schema to the user but it will still create the tables in the dbo schema. I´m still trying to figure out how to change the default schema to something else than dbo.
Assign the role membership as shown:

In Your Symfony Project

After you have created the database, the login and the user you will be able to use the database with Symfony.

symfony doctrine:build --all-classes --model --forms --filters --sql
symfony doctrine:insert-sql
symfony doctrine:data-load

But what happens if you make changes to your schema. You cannot run the insert-sql task because the tables already exist. You´ll have to use the create-model-tables task. The inconvenience with this is that you have to explicitly tell the task what model tables to recreate; this is kind of a hassle. I’m currently working in a way to create the equivalent task to doctrine: build --all for MS SQL Server, as soon as I´ve a working task I´ll post it.

symfony doctrine:build --all-classes --model --forms --filters --sql
symfony doctrine:create-model-tables User Phone
symfony doctrine:data-load