Every day I spend a good amount of time with my regular and new clients, helping them with SQL Server Performance Tuning issues. Today, in this blog, I will discuss a situation faced where I was engaged in Comprehensive Database Performance Health Check. I saw below message on client’s SSMS while he was trying to create a table – You are not logged on as the database owner or system administrator.
The developer was trying to create a table in the database and received below pop-up.
Here is the text of error message.
You are not logged on as the database owner or system administrator. You might not be able to save changes to tables that you do not own.
I was surprised that he happily ignored it and moved ahead. After the main performance issue was resolved, I asked the developer about the error message. He gave me more information and I decided to do more research and blog about it.
Steps to Reproduce
- Create a new login with below permissions in the database. Make sure it is having “public” in “Server Roles”
- Once the login is created with above-mentioned permissions then login to SSMS, go inside the database and try to create a table using SSMS.
And you should get the same error.
As per documentation from Microsoft, it is “By-Design” behavior and it is already documented here.
Because you are not logged on as the system administrator, database owner, or a user that is a member of the db_owner role, you have limited privileges to the database. The privileges you have are determined by the permissions granted to your login ID and the privileges granted to the roles that your login ID is a member of.
Even though you are not the database owner, you will still be able to use any tables that you have permissions to see. For example, you can create diagrams using such tables. However, you won’t be able to perform all edits. Certain edits require SQL Server CREATE TABLE permission, which gives you permission to create new tables and modify tables that you own.
Even if you have CREATE TABLE permission, there are limitations to the modifications you can make. Remember, as you modify an existing table or design a new one, your work can induce attendant modifications in other tables. For example, if you change the data type of a foreign-key column, the corresponding column in the primary-key table will be automatically modified by the Visual Database Tools. If you do not own the primary key table, and you are not logged in as the system administrator, database owner, or a user that is a member of the db_owner role, your modification will fail.
Reference: Pinal Dave (https://blog.sqlauthority.com)
