locked
2008 SP1 crashing on schema compare

    Question

  • In doing a schema compare of a database project to a reasonably tightly secured SQL Server 2005 database, I'm getting a crash when schema compare is reading the permissions of the SQL Server 2005 database.

    I've uploaded a diagnostic trace to http://dotnetperformance.com/devenv_11_18_2008_17_36_10.zip.

    Any help appreciate.

    Thanks

    Nick Wienholt
    C++ MVP
    Tuesday, November 18, 2008 7:28 AM

Answers

  • Hi Nick,

    To reverse engineer, compare, or import the schema of a database using VSTSDB GDR the database user/login for the connection must have the minimum permissions below (per readme file).

    The user/login also must have execute access for the extended stored procedure: xp_instance_regread.  This is granted by default, but some sysadmins may revoke.

    Thanks.

    Import Database Minimum Permissions

    To successfully import database objects into a project from an existing database, adequate permissions must be granted to the user or login used to connect to that database. The following lists the minimum permissions required to import databases objects and settings.

     

    SQL 2000 Database project

    User is a member of the database and GRANT SELECT on dbo.sysproperties  to user.

     

    SQL Server 2000 Server Project

    GRANT SELECT on dbo.sysproperties to user. User must be a member of the sysadmin role.

     

    SQL 2005 Database Project

    GRANT VIEW DEFINITION on database to user.  

    NOTE: If insufficient privileges exist for the user, you will not receive an error message when importing from a 2005 database. Instead you receive “Import successfully completed”, but no database objects will exist in the project.

     

    SQL Server 2005 Server Project

    GRANT VIEW ANY DEFINITION on the server to user. If importing linked servers, GRANT ALTER ANY LINKED SERVER to user.

     

    SQL 2008 Database Project

    GRANT VIEW DEFINITION on the database to user. If importing database encryption keys, user must also be granted VIEW SERVER STATE. 

     

    SQL Server 2008 Server Project

    GRANT VIEW ANY DEFINITION on the server to user. If importing linked servers, GRANT ALTER ANY LINKED SERVER to user. If importing database encryption keys, user must also be granted VIEW SERVER STATE.

    • Marked as answer by Nick_Wienholt Wednesday, November 19, 2008 6:57 AM
    Tuesday, November 18, 2008 5:34 PM
    Moderator

All replies

  • Hi Nick,

    To reverse engineer, compare, or import the schema of a database using VSTSDB GDR the database user/login for the connection must have the minimum permissions below (per readme file).

    The user/login also must have execute access for the extended stored procedure: xp_instance_regread.  This is granted by default, but some sysadmins may revoke.

    Thanks.

    Import Database Minimum Permissions

    To successfully import database objects into a project from an existing database, adequate permissions must be granted to the user or login used to connect to that database. The following lists the minimum permissions required to import databases objects and settings.

     

    SQL 2000 Database project

    User is a member of the database and GRANT SELECT on dbo.sysproperties  to user.

     

    SQL Server 2000 Server Project

    GRANT SELECT on dbo.sysproperties to user. User must be a member of the sysadmin role.

     

    SQL 2005 Database Project

    GRANT VIEW DEFINITION on database to user.  

    NOTE: If insufficient privileges exist for the user, you will not receive an error message when importing from a 2005 database. Instead you receive “Import successfully completed”, but no database objects will exist in the project.

     

    SQL Server 2005 Server Project

    GRANT VIEW ANY DEFINITION on the server to user. If importing linked servers, GRANT ALTER ANY LINKED SERVER to user.

     

    SQL 2008 Database Project

    GRANT VIEW DEFINITION on the database to user. If importing database encryption keys, user must also be granted VIEW SERVER STATE. 

     

    SQL Server 2008 Server Project

    GRANT VIEW ANY DEFINITION on the server to user. If importing linked servers, GRANT ALTER ANY LINKED SERVER to user. If importing database encryption keys, user must also be granted VIEW SERVER STATE.

    • Marked as answer by Nick_Wienholt Wednesday, November 19, 2008 6:57 AM
    Tuesday, November 18, 2008 5:34 PM
    Moderator
  • Hi Barclay,

    Adding GRANT VIEW DEFINITION to the user for the SQL Server 2005 connection did the trick - the schema compare now works without crashing. 

    Just for the record, this was with the current shipping version of VSTS Data (2008 with SP1), not GDR, and the user experience was a devenv crash, not any warning message.

    Thanks for the reply.

    Nick

    Wednesday, November 19, 2008 6:57 AM