The common error when you try to access any table or to perform select on SQL Server is “ Microsoft sql server error 229 ”. This error is related with the permission on certain database. The user don’t have permission for select but also to access certain objects. This article will explain and try to fix this error.
Microsoft SQL Server Error 229
Even if you created the user and given him the owner rights for the database, you can login as user but cannot access the databases. It looks like the user has not the necessary permission on the database. And you could check if the he is assigned to the proper Server Role, User Mappings and the schema he has permissions to. Go to ‘permissions’ in the ‘properties’ of the database to configure the user permission.
Microsoft SQL Server error 229
Error Message Case 1
Error:
ADDITIONAL INFORMATION:
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)
Solution:
There are numerous reasons for this error but most common is when db_denydatawriter and db_denydatareader is assigned to login. DENY permission overrides any access permission.
So remove the ticket from db_denydatawriter and db_denydatareader and the user can perform the select.
Remove the ticket from db_denydatawriter and db_denydatareader
Error Message Case 2
Error:
The EXECUTE permission was denied on the object ‘sp_add_jobstep’, database ‘msdb’, schema ‘dbo’. (Microsoft SQL Server, Error: 229)
Solution:
This error appears when trying to create a Job on server where you don’t have permission to create a job.
In order to get access to SQL Agent jobs you need to be part of 3 Database roles on msdb i.e. 1.SQLAgentOperatorRole 2. SQLAgentReaderRole 3. SQLAgentUserRole.
To View if you have Execute permission on “SP_add_jobstep” follow below steps.
- Connect to SQL server and Expand Databases, go to msdb and then security –> Roles –> Database Roles.
- Right click the any of the above Role and open properties.
Check other errors on our specific category “SQL Server”
Please comment below if you need support regarding ” Microsoft SQL Server Error 229 “.
