Error when generating script from SSMS

  • I login to SQL Server 2008 with a user that has dbowner permission on a database 'X'. Then I try to generate script of a table using Tasks --> Generate Script. I get the below error.

    The server principal "USERNAME" is not able to access the database "Y" under the current security context. (Microsoft SQL Server, Error: 916)

    The problem with is that, the DatabaseName 'Y' that shows up in the error message is never accessed while scripting from X database.

    Is this a bug ? Has anyone encountered the same issue before ? Any help is appreciated.

    Thanx in Advance !

    Sanz
  • These screen shots might help.

    Sanz
  • There is an object (in the db you are scripting) that is referencing an object in that other database. That reference creates a dependency and the user scripting this database must not have access to the object in the other database.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Thanks much. I gave the user db_reader permission on the second database and it worked like a charm !

    Is there a script to find the object dependency ?

    Sanz
  • You can query the sys.sql_expression_dependencies catalog view.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply