What are the challenges you face when working across database platforms? Take the survey

One Database Fails

Grant FritcheyGrant Fritchey Posts: 72
edited May 16, 2008 11:33AM in SQL Prompt Previous Versions
I'm running 3.8 pro (although I had the same error in the previous version). When I try to connect to one database, and so far only this one database, I get an error stating that SQL Prompt can't process the schema. Here's the detailed error:

RedGate.SqlPrompt.Engine.Cache.DatabaseRefreshException ---> System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.String'.
at k.v(SqlConnection )
at k.a(j )
at RedGate.SQLCompare.Engine.Database.RegisterForSqlPrompt(ConnectionProperties connectionProperties, Options options, Boolean includeSystemObjects)
at cy.b(IAuthenticationTokenProvider )
--- End of inner exception stack trace ---
at cy.b(IAuthenticationTokenProvider )
at cy.a(IAuthenticationTokenProvider )
at cy.GetMetaData(IAuthenticationTokenProvider provider)
at M.b(G , IAuthenticationTokenProvider , Boolean , EventHandler`1 , EventHandler`1 , EventHandler`1 , EventHandler`1 )


  • Options
    chris.buckinghamchris.buckingham Posts: 59
    edited May 16, 2008 11:41AM
    There is a possibility is that you have insufficient access rights on the database. On SQL Server 2000 you need dbo access, on SQL Server 2005 and 2008, you don’t but you do need some additional permissions.

    For anyone out there working with SQL Server on a large or mission critical system it's quite likely that, unless you're the DBA, you're not going to have dbo access to the databases you're working with, particularly if you're working directly with the production servers. Unsurprisingly then we've had a number of complaints from people finding themselves in exactly this situation who want to be able to use SQL Prompt 3, but can't because it generally doesn't work unless you're dbo.

    Fortunately my colleague Jon this morning let me know that a solution is at hand for SQL Server 2005 at least, although you're still going to have to be nice to your DBA to make this work (I'd suggest that beer often helps). What you need to do is ask him or her to grant you the VIEW DEFINITION permission on the database of interest. What this means is that you'll be able to see all the meta-data for any object defined in the database, but you won't have access to the objects themselves, so for example you won't be able to read any data from a table unless you have the permissions required to do so.

    For example, say your username is "bill" and you need access to the AdventureWorks database, then your DBA would need to execute the following command:

    GRANT VIEW DEFINITION ON Database::AdventureWorks TO bill

    (Please see our developer's blog post for the full article:
    http://www.simple-talk.com/community/bl ... 19421.aspx)
    Chris Buckingham
    Red-Gate support
  • Options
    Postscript: Customer found that GRANT VIEW DEFINITION was the problem and it now works as expected.
    Chris Buckingham
    Red-Gate support
Sign In or Register to comment.