locked
Login ID lost DB Owner privileges RRS feed

  • Question

  • I have a J2EE application server that connects to a SQL 2008 database using its own login ID.  In order for the application to run, it needs db owner privileges and security admin privileges. 

    I have been having problems with periodic crashes during the last few weeks, and in each case, when the server crashed, it was because the database connection was unexpectedly closed.  Rebootig the server always fixed this error.  However, this morning it crashed again, and this time a hardware reboot did not fix the problem.  Upon looking at the log for the application, I discovered that the application could not connect to the database, i.e. it was failing to log in.  So I went into Studio and tried to connect using the same login ID and password - and the database was inaccessible.

    It turns out that the User ID had somehow lost its db owner privileges.  When I ran an sp_changedbowner and restored the db owner privileges, everything was fine again.  (Note:  the securityadmin privilege was still there.)

    My question is:  can anyone think of why a login ID would suddenly lose its db owner privileges, other than someone running an sp_changedbowner?  We couldn't see anything in the logs, but I'm not sure the logs are set up to trap for that kind of event.

    Thanks in advance

     

    Shannon

    Wednesday, April 6, 2011 6:49 PM

Answers

  • Obviously this should not happen, so something weird occured. So just a wild guess, but perhaps your application is written is such a way, that when it couldn't use the old login, it deleted the old login, and then created a new one. Since the new login is not the same as the old login of the same name, it didn't have the db_owner role membership. It may even have tried to recreate the database, which worked the first time (and the login who created the db owned it) but it failed the second time because the database already existed. Error logs, in particular your old error logs from before the reboot, or the first startup after the reboot, might have interesting info.
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Marked as answer by WeiLin Qiao Thursday, April 14, 2011 1:27 AM
    Wednesday, April 6, 2011 7:26 PM
  • Additionally - I would not set the application login as the owner of the database.  I would set the owner of the database to another login and add the application login to the db_owner role.
    Jeff Williams
    • Marked as answer by WeiLin Qiao Thursday, April 14, 2011 1:27 AM
    Thursday, April 7, 2011 12:35 AM

All replies

  • Obviously this should not happen, so something weird occured. So just a wild guess, but perhaps your application is written is such a way, that when it couldn't use the old login, it deleted the old login, and then created a new one. Since the new login is not the same as the old login of the same name, it didn't have the db_owner role membership. It may even have tried to recreate the database, which worked the first time (and the login who created the db owned it) but it failed the second time because the database already existed. Error logs, in particular your old error logs from before the reboot, or the first startup after the reboot, might have interesting info.
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Marked as answer by WeiLin Qiao Thursday, April 14, 2011 1:27 AM
    Wednesday, April 6, 2011 7:26 PM
  • Additionally - I would not set the application login as the owner of the database.  I would set the owner of the database to another login and add the application login to the db_owner role.
    Jeff Williams
    • Marked as answer by WeiLin Qiao Thursday, April 14, 2011 1:27 AM
    Thursday, April 7, 2011 12:35 AM