Compatibility Level Field Is Disabled In SQL Server

Problem

Once when I was upgrading an SQL Server and its databases to higher version, I tried to change the compatibility level of one of the database from management studio database properties screen and saw that the Compatibility Level options field is disabled and grayed out as seen in this screen shot. I reviewed my logged in user’s security settings and confirmed that the user is a db_owner of the database. I could not understand why the field is disabled in management studio for the user with database owner role.

Workaround

Then, I tried using the below T-SQL script to change the compatibility. The T-SQL statement ran and gave a message Commands completed successfully. Then, I refreshed the database in object explorer and went back to the database properties screen and verified the compatibility level. It is now showing the new compatibility level, but the field is still disabled. It’ is strange that I can alter the database property using T-SQL but not from the management studio’s GUI for the same logged in user with db_owner role.

ALTER DATABASE [DnnDev.me]
SET COMPATIBILITY_LEVEL = 140;

/* Messages */
Commands completed successfully.

Another Workaround

To test further, I logged in to the SSMS using a sysadmin login. The compatibility level field is now enabled and I’m able to change it.

Conclusion

Both the logins are mapped to db_owner database role membership for the database. The main difference is, the login which I have used initially is mapped to public server role and the second login I have used is mapped to sysadmin server role. So I guess, management studio is overriding the database level role with the server role.

Reference

• About changing compatibility level of database at MSDN.