As a SQL Server professional, I often come across various performance tuning issues faced by my clients. Recently, I encountered a situation during a Comprehensive Database Performance Health Check where a developer was trying to create a table in the database but received an error message stating, “You are not logged on as the database owner or system administrator.”
Curious about this error message, I decided to delve deeper into the issue and share my findings in this blog post.
The Error Message
The error message the developer encountered was:
“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.”
Upon further investigation, I discovered that this error message is a “By-Design” behavior in SQL Server, as documented by Microsoft.
Understanding the Error
When 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.
Although you may not be 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 these tables. However, certain edits, such as creating new tables or modifying tables that you do not own, require the SQL Server CREATE TABLE permission.
Even if you have the CREATE TABLE permission, there are limitations to the modifications you can make. It’s important to remember that modifying an existing table or designing a new one can induce attendant modifications in other tables. For instance, 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. However, 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.
Reproducing the Error
If you want to reproduce the error, follow these steps:
- Create a new login with the necessary permissions in the database, ensuring it has the “public” role in “Server Roles”.
- Login to SQL Server Management Studio (SSMS) using the newly created login.
- Go inside the database and try to create a table using SSMS.
- You should receive the same error message.
It’s important to note that this behavior is intentional and documented by Microsoft.
Understanding SQL Server permissions is crucial for developers and administrators to ensure smooth database operations. By being aware of the limitations and privileges associated with different roles and permissions, you can avoid unnecessary errors and ensure efficient database management.
That’s all for today’s blog post. I hope you found this information helpful in understanding SQL Server permissions. Stay tuned for more articles on SQL Server and database management!