Search code examples
sql-server-2008visual-studio-2013database-schemaschema-comparesqlproj

Why does Visual Studio 2013 Schema Compare include Permission Statements for each object definition and treat them as different from the project?

I recently updated one of my projects to Visual Studio 2013. The update changed my .dbproj into a .sqlproj to make it compliant with the new SQL project format.

After the conversion, I ran a schema compare against my SQL project and my SQL 2008 production database and it now considers virutally every object to be different. Upon investigation through the schema compare tool, it appears that the difference lies in the fact that every object definition also includes statements for the permissions granted on that object to every role or user.

Thus, the server side looks like this:

CREATE PROC MyCoolProc
BEGIN
  --some code
END
GO

GRANT EXECUTE
    ON OBJECT MyCoolProc TO MyAwesomeUser
    AS [Schema];
GO

and the client side for the same object looks like this:

CREATE PROC MyCoolProc
BEGIN
    --some code
END

Why is this? This didn't occur when I ran schema compare using Visual Studio 2010. In addition, I looked through all of the options for SQL compare tool and I could not find one that would "ignore permission statements for objects". Can anyone help?

EDIT

Just to ensure that this is an issue with Visual Studio's SQL Compare tool and not the SQL Server itself, I re-ran the compare in Visual Studio 2010 between my staging and production database and the object definitions do not include the object permissions like they do in Visual Studio 2013.

Solution

  • It appears that under the options for Visual Studio 2010's SQL compare tool "Schema Compare Options" window, the tab "Object Types" has a checkbox for all of the object types you want to ignore. It has "Extended Properties" and "Permissions" checked by default.

    As compared to Visual Studio 2013's SQL compare tool "Schema Compare Options" window whose tab "Object Types" has a checkbox for all of the object types you want to include in the comparison. Under the "Application-scoped" tree node, all object types are checked by default. Unchecking "Extended Properties" and "Permissions" causes this SQL Compare tool to behave exactly like the Visual Studio 2010 compare tool.

    This prevents the extraneous permissions and extended properties from being returned by the server as part of each object's definition. Hopefully, this will help someone else who comes across this same issue.