SQL Server Error 229: The SELECT permission was denied on the object ‘databases’, database ‘mssqlsystemresource’, schema ‘sys’.

by

SQL Server error 229 occurs when users try to execute a SQL query. It may also occur while trying to connect to the database server. The error message displayed is “The SELECT permission was denied on the object.” The user account executing the query or stored procedure lacks the necessary permissions, causing the error. In this post, we will discuss the causes of this error, and the steps you can take to resolve it.

Why Does SQL Server Error 229 Occur?

The SELECT permission is a fundamental permission in SQL Server, and without it, users cannot read data from the database.

There are a few reasons why a user may not have the SELECT permission:

  1. Lack of ownership: If the user trying to execute the query or stored procedure is not the owner of the database object, they may not have the SELECT permission due to a lack of ownership..
  2. Incorrect Role Assignment: If the user is not assigned to the correct role, they may not have the SELECT permission due to incorrect role assignment.
  3. User account restrictions: If the user account has been restricted, then the user may not have the SELECT permission

Issue Faced:

One of customer was having issue rtying to connect using a SQL login to his server.

The SELECT permission was denied on the object ‘databases’, database ‘mssqlsystemresource’, schema ‘sys’. (Microsoft SQL Server, Error: 229)

The SELECT permission was denied on the object ‘configurations’, database ‘mssqlsystemresource’, schema ‘sys’. (Microsoft SQL Server, Error: 229)

The login functions when higher permissions like sysadmin are granted to it, but it cannot receive sysadmin privileges because it is a part of a database role with only view permissions.

We verified if the error was caused by an explicit deny permission granted to the login, but observed nothing of the sort at the login and role assignment level..

How to Resolve SQL Server Error 229?

There are several steps you can take to resolve SQL Server error 229:

  1. Grant the SELECT permission: The easiest way to resolve this error is to grant the SELECT permission to the user. You can do this by using the GRANT statement in SQL Server.
  2. Assign the user to the correct role: If the user is not assigned to the correct role, then assign the user to the role that has the SELECT permission.
  3. Remove restrictions on the user account: If the user account has been restricted, then remove the restrictions to allow the user to access the database object.
  4. Change the ownership of the database object: If the user is not the owner of the database object, then change the ownership to the user..

Resolution for our issue:

Since no explicit deny was observed at login or role level, I looked at other permissions that may have explicit deny on the login. All logins also have Public role permission be default, so I scripted out public role permissions with below code.

SELECT p.permission_name
	,[schema] = SCHEMA_NAME(o.schema_id)
	,object_name = o.name
	,role_name = u.name
FROM master.sys.database_permissions p
INNER JOIN master.sys.database_principals u ON p.grantee_principal_id = u.principal_id
INNER JOIN master.sys.all_objects o ON o.object_id = p.major_id
WHERE p.grantee_principal_id IN (
		0
		,2
		)
ORDER BY u.name
	,o.schema_id
	,o.name
	,p.permission_name;

Same script was executed on a new server to compare permissions.

We observed that the public role was missing 2 permissions select on databases and configurations for public role.

USE [master]; grant SELECT ON [sys].[configurations] TO public;
USE [master]; grant SELECT ON [sys].[databases] TO public;

Post this the as soon as we tested the login started working.

Conclusion

SQL Server error 229 is caused by a lack of permissions. By following the steps mentioned, you can successfully resolve SQL Server error 229.

Post Views: 1,240