SQL Server Error : 229, Severity: 14. The %ls permission was denied on the obj

SQL Server Error : 229 Details

SQL Server Error: 229
Severity: 14
Event Logged or not: No
Description:
The %ls permission was denied on the object ‘%.*ls’, database ‘%.*ls’, schema ‘%.*ls’.
Severity 14 Description:
Indicates security-related errors, such as permission denied.

Reading sql server error log location from SQL Query

Identifying SQL Server Error Log File used by SQL Server Database Engine can be done by reading SQL Server Error Logs. DBA can execute the XP_READERRORLOG extended stored procedure to read the SQL Server Error Log and search for its location used by the instance of SQL Server.

USE master
Go
xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc'
Go

 

Error 229 in Microsoft SQL Server – This error is caused by a permissions issue on a specific database. The user does not have the ability to not only select but also access certain objects. This article will explain and attempt to correct this error.
Error 229 in Microsoft SQL Server
Even if you created the user and granted him database owner rights, you will be unable to access the databases. It appears that the user does not have the necessary database permissions. You could also check to see if he is assigned to the correct Server Role, User Mappings, and the schema to which he has permissions. To configure the user permission, navigate to ‘permissions’ in the database’s ‘properties.’

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

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

or  SQL Agent job step failure resulting this error

The EXECUTE permission was denied on the object ‘sp_add_jobstep’, database ‘msdb’, schema ‘dbo’. (Microsoft SQL Server, Error: 229)

Solution for Resolving the Error

Error 229 in Microsoft SQL Server – This error is caused by a permissions issue on a specific database. The user does not have the ability to not only select but also access certain objects.
Error 229 in Microsoft SQL Server
Even if you created the user and granted him database owner rights, you will be unable to access the databases. It appears that the user does not have the necessary database permissions. You could also check to see if he is assigned to the correct Server Role, User Mappings, and the schema to which he has permissions. To configure the user permission, navigate to ‘permissions’ in the database’s ‘properties.’

Connect to the SQL server and expand Databases, then msdb, and finally security –> Roles –> Database Roles.
Right-click any of the above Roles to open their properties.

To View if you have Execute permission on “SP_add_jobstep” follow below steps.

1. Connect to SQL server and Expand Databases, go to msdb and then security –> Roles –> Database Roles.
2. Right click the any of the above Role and open properties.

 

 

SQL Server Error Code and solution summary

SQL Server Error: 229
Severity: 14
Event Logged or not: No
Description:
The %ls permission was denied on the object ‘%.*ls’, database ‘%.*ls’, schema ‘%.*ls’.

Make sure the permissions are corrected to rectify this Error.