question

SahaSaha-5270 avatar image
0 Votes"
SahaSaha-5270 asked ErlandSommarskog answered

Cannot access the database with the assigned privilege

100324-cannotaccessdatabase.jpg


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.

sql-server-general
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This statement does not work,
USE YourDBName

So what error message do you get in this case?

Attach/detach is not going to fix anything. And if you are not sysadmin, you may not even have permissions to do it.

0 Votes 0 ·

Msg 916, Level 14, State 1, Line 1
The server principal "username" is not able to access the database "dbname" under the current security context.
Msg 15109, Level 16, State 1, Line 3
Cannot change the owner of the master, model, tempdb or distribution database.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @SahaSaha-5270,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.