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
Thanks in advance
View Complete Post