locked
EXECUTE permission was denied RRS feed

  • Question

  • I have a SQL login account that I use in web.config and every time SQL fails over it looks like that account on the new Principle does not have sufficient permissions on the database. If i remove the user account and re-add with proper permissions it works, if I fail over the database again that SQL user account does not have proper permissions.
    Monday, March 22, 2010 2:02 PM

Answers

  • You are hitting the problems of mismatch users in SQL server. When a database is failed over / restored only the user gets created  and the link between user and login in server B is broken so now you need to run the sp_change_users_login ‘report’ to get the list of mismatch users ( users that doesn’t have a login with correct SID) in the current db .

    You need to fix them manually using sp_help_revlogins stored procedure which scripts the login from primary server with its SID and the same script can be executed in secondary server. Now we have to drop the existing user in the db and recreate the user from newly created login.

     

    Please let us know if you cant still get my point here


    Thanks, Leks
    Monday, March 22, 2010 9:03 PM
    Answerer
  • Hi,


    To troubleshoot the issue, try the following steps:
    1. For two server instances to communicate in a database mirroring session, the login account of each instance requires access to the other instance. Also, each login account requires connect permission to the database mirroring endpoint of the other instance. For more information, see Setting Up Login Accounts for Database Mirroring:
    http://technet.microsoft.com/en-us/library/ms366346.aspx

    2. Since a database user can become orphaned if after a database is restored, attached, or copied to a different instance of SQL Server, I suggest you generate a script for the login and permission objects in the original of the database. You can use the Generate Scripts Wizard, and in the Choose Script Options dialog box, set the Script Logins option and the Script Object-Level Permissions option to True. Then execute the scrip on the mirror server instance.


    If there are any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Wednesday, March 24, 2010 5:40 AM

All replies

  • You are hitting the problems of mismatch users in SQL server. When a database is failed over / restored only the user gets created  and the link between user and login in server B is broken so now you need to run the sp_change_users_login ‘report’ to get the list of mismatch users ( users that doesn’t have a login with correct SID) in the current db .

    You need to fix them manually using sp_help_revlogins stored procedure which scripts the login from primary server with its SID and the same script can be executed in secondary server. Now we have to drop the existing user in the db and recreate the user from newly created login.

     

    Please let us know if you cant still get my point here


    Thanks, Leks
    Monday, March 22, 2010 9:03 PM
    Answerer
  • Hi,


    To troubleshoot the issue, try the following steps:
    1. For two server instances to communicate in a database mirroring session, the login account of each instance requires access to the other instance. Also, each login account requires connect permission to the database mirroring endpoint of the other instance. For more information, see Setting Up Login Accounts for Database Mirroring:
    http://technet.microsoft.com/en-us/library/ms366346.aspx

    2. Since a database user can become orphaned if after a database is restored, attached, or copied to a different instance of SQL Server, I suggest you generate a script for the login and permission objects in the original of the database. You can use the Generate Scripts Wizard, and in the Choose Script Options dialog box, set the Script Logins option and the Script Object-Level Permissions option to True. Then execute the scrip on the mirror server instance.


    If there are any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Wednesday, March 24, 2010 5:40 AM