Cannot access the database with the assigned privilege

Question

Sa is disable and do not have the password. My user does not have sysadmin privileges and cannot add account with sysadmin privileges.
This statement does not work,
USE YourDBName
GO
sp_changedbowner 'sa'
GO

This is SQLServer 2014,
Check this link https://dba.stackexchange.com/questions/61722/cannot-access-properties-page-of-a-database
Will the detach and attach the database fix this issue.

Answer 1

The server principal "username" is not able to access the database "dbname" under the current security context.

Well, that explains it all, and there is not much we can do about it here. If you don't have access to that database, and you are not sysadmin, and you need permission to that database, you will need to find someone who can grant you those permissions.

Answer 2

Hi @Avyayah ,

> Property Owner is not available for Database '[database name]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights

Please ran the system stored procedure sp_helpdb to check the database properties, check the value of owner column.

sp_helpdb  YourDBName  

If the owner column was set to UNKNOWN, you can quickly resolve this issue by running following command in your query window. Please ask help for the sysadmin user to help you resolve your issue, since your account is not a sysadmin account, you do not have the permission to run the query.

USE YourDBName  
GO  
sp_changedbowner 'sa'  
GO  

Run this query requires TAKE OWNERSHIP permission on the database. If the new owner has a corresponding user in the database, requires IMPERSONATE permission on the login, otherwise requires CONTROL SERVER permission on the server. Refer to MS document sp_changedbowner (Transact-SQL)

Please refer below blogs to get more information;

Property Owner is not available for Database SSMS error
SQL SERVER – Fix – Error – Property MemoryAllocatedToMemory OptimizedObjectsInKB is not available for Database. This property may not exist for this object, or may not be retrievable due to insufficient access rights.

By the way, suggest you using the latest version SSMS 18.9.1 to connect to SQL server 2014 as Tom mentioned.

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.

Answer 3

The error you posted is caused when using an old version of SSMS to connect to a newer version of SQL Server.

Please download and install the current version of SSMS.
https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15