mibuso.com

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

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

Windows or SQL logins?

Windows authentication

Steve

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

No, sorry

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?

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.

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