You must have encountered one of these errors or more than one at many times whenever there is a new object created or procedure has been changed(dropped and recreated). Mostly DBAs handle permissions at role level however in many cases some of the objects or logins(or users) are not part of role which caters to retention of permission.
How do you ensure that the object you are selecting from or deleting from or performing any action on certain object e.g. table, view, stored procedure are already available for you to perform that action. We often deploy the application in Production environment without checking the permission of application user and at run time the application fails. In some cases, development team doesn’t capture complete log as to why application failed and it becomes hard for DBA/support team to troubleshoot and find the exact root cause therefore, it is imperative to check the permission level of login(user) which is used by application to connect to database and perform the operation.
There is a single command in SAP Sybase ASE which caters to all these requirement and is mentioned as below:
sp_helprotect
This is one of the most powerful command of Sybase ASE related to access permission and it can list access on multiple layers/levels. Below is list of layers/levels for which access could be checked:
- Table
- View
- Stored Procedure
- Login
- User
- Role
- Function
Please note that the command returns access permission based on database context and hence you should change database in which you wish to run the command as below:
use database_name
go
sp_helprotect object_name
go
If the command is run without any parameter, it returns all the permissions granted in that database. In case, user doesn’t have any explicit permission and is just added as a user in certain database, running command for that user will return all the roles assigned to public role which is inherited by all the logins and roles.
Please note that, the command could take a while depending on the number of object and permission granted. In case, login is aliased with dbo, can return many rows and could take many minutes. It doesn’t cause any locking/blocking on the database however you would need to wait to see the final output.
The output columns – column and grantable is of significant importance as it specifies whether any specific column could be accessed by a login or all columns could be accessed, grantable column “TRUE” means that login can grant permission to another user on that object and column(if ALL is not specified in column, by default it is ALL unless specified).
Please leave your comment if you liked this post or have any feedback.