$ndo$shadow trouble

Toddy_BoyToddy_Boy Posts: 180Member
edited 2012-02-08 in SQL General
After using the SQL 2008 Copy Database wizard I now have a Copy of the Live database for testing/training etc which will be refreshed every night. I can gain access to the database no problem but when non db owners try to access it using Nav 2009 R2 they get an error
Cannot set application role '$ndo$shadow' because it does not exist or the password is incorrect

The application role does exist and has a password according to SSMS.

Security is Standard in the Live and Copy databases, I've synchronised the logons and users have permissions to both databases.

I've read about changing the security model to Enhanced and back to Standard as this will be a scheduled job I don't want to be running extra commands that could lock the job as can happen when changing the security model.

Any ideas why an exact copy of the database shouldn't act in the same way as the source database?

Steve
Life is for enjoying ... if you find yourself frowning you're doing something wrong

Comments

  • David_SingletonDavid_Singleton Posts: 5,367Member
    Windows or SQL logins?
    David Singleton
  • Toddy_BoyToddy_Boy Posts: 180Member
    Windows authentication

    Steve
    Life is for enjoying ... if you find yourself frowning you're doing something wrong
  • eYeeYe Posts: 166Member
    Hi,

    I am doing the exact same thing, have you guys found any solution to this?

    Synchronizing the logins doesn't work as it complains that a login (the owner of the db) already exists in the database...

    Kind Regards,

    Ewald
  • Toddy_BoyToddy_Boy Posts: 180Member
    No, sorry :cry:
    Life is for enjoying ... if you find yourself frowning you're doing something wrong
  • davmac1davmac1 Posts: 1,193Member
    Are you restoring to the same sql server instance?
    If not, you may have a mismatch between your instance security users and your database security users.

    Also, are you on the same Windows domain?
  • eYeeYe Posts: 166Member
    Same domain different servers/instances.

    It was running perfectly when the jobs were created to run on a Windows account, a month later suddenly started getting permission errors so the copy didn't happen and the client denies changing permissions for the login.

    So recreated the jobs using a db login with the same password in both instances, on both servers it has the sysadmin role.
    And it is set to copy the logins from source to destination server.
  • herrJonesherrJones Posts: 8Member
    You can use the sp_change_users_login stored procedure from SQL server.

    That did the trick for me...

    To report the 'unmapped' users: exec sp_change_users_login 'Report'

    Check the Microsoft Help page for more details.

    Jan
Sign In or Register to comment.