Adding a new stored procedure to an existing Visual Studio 2008 database project

Question

I have imported an existing SQL Server 2008 database to a Visual Studio 2008 database project, to version control database objects and perform database code migration. This works fine and I can build and deploy to dev, test, staging, live etc.

Now I want to add a new stored procedure (with a grant statement) to the existing database project and cannot see an ideal way to do this? I can either: -

1. Create the stored procedure in the development database, reverse engineer a script, and “import script” to my Visual Studio project.
2. Create the stored procedure directly in my Visual Studio project, but the grant statement raises an error there, so I will have to manually edit the xml in the “Database.sqlpermissions” script.
3. Create the stored procedure directly in my Visual Studio project. Then add the GRANT statement to the “Script.PostDeployment.sql” file. But now my permissions are spread across two files “Database.sqlpermissions” and “Script.PostDeployment.sql”.

What is the preferred way to add a new stored procedure with a grant statement to an existing Visual Studio 2008 database project?

Answer 1

My understanding is that the preferred location for permissions is the Database.sqlpermissions file. It can be a pain to work with IMHO because it forces you to switch from using the traditional syntax of GRANT EXECUTE ON OBJECT to GRANTEE to working with XML. IntelliSense makes it easier to get the syntax correct in the file, but it just isn't as easy to work with as writing the statement yourself, or even using the GUI in SSMS.

The syntax difference aside, the biggest reason I don't like the separation of concerns here is that it is difficult to see whether or not an object has an associated grant statement in the Database.sqlpermissions file. Maybe there is a way within Visual Studio and I haven't seen it, but it would be nice to be able to look at the properties of an object within the project and see a list of permissions for the object - similar to SSMS. (If anyone knows how to do this within VS now please let me know!)