Today at a client, when attempting to pull up the properties for a databsae through SQL Server Management Studieo (SSMS), we were greeted with the following error:
Initially we assumed this was due to insufficient access rights, but after verifying we had sysadmin credentials, we still were unable to see the properties.
As it turns out, the login account that owned the database (i.e. the account that initially was 'dbo' of the database) had been removed, leaving the server with 'orphaned' databases. This can easily be solved by issuing the following command against the affected database(s)
EXEC sp_changedbowner 'sa'
I should add that the server in question was a 2005 machine. I tried replicating the behaviour on my SQL 2008 local installation, but was unable to drop a user that was still owning a database:
'Drop failed for Login 'Test'. (Microsoft.SqlServer.Smo)
Additional Information:
Login 'test' owns one or more database(s). Change the owner of the database(s) before dropping the login. (Microsoft SQL Server, Error: 15174).
Initially we assumed this was due to insufficient access rights, but after verifying we had sysadmin credentials, we still were unable to see the properties.
As it turns out, the login account that owned the database (i.e. the account that initially was 'dbo' of the database) had been removed, leaving the server with 'orphaned' databases. This can easily be solved by issuing the following command against the affected database(s)
EXEC sp_changedbowner 'sa'
I should add that the server in question was a 2005 machine. I tried replicating the behaviour on my SQL 2008 local installation, but was unable to drop a user that was still owning a database:
'Drop failed for Login 'Test'. (Microsoft.SqlServer.Smo)
Additional Information:
Login 'test' owns one or more database(s). Change the owner of the database(s) before dropping the login. (Microsoft SQL Server, Error: 15174).