Hello everyone, today we are going to tell you how to know if I have permissions in SQL Server. For this we will use the function that the system gives us HAS_PERMS_BY_NAME .
You can find all the complete information in the official documentation at the following link:
https://docs.microsoft.com/es-es/sql/t-sql/functions/has-perms-by-name-transact-sql?view=sql-server-ver15
Although we are going to see the most common operations.
Syntax
The general syntax of the function is:
HAS_PERMS_BY_NAME (securable, securable_class, permission [, sub-securable] [, sub-securable_class])
Inside the syntax:
- securable : It is the element on which we want to know if we have permissions or not. If the element is of type server, this value must be specified to NULL.
- securable_class : It is the type or class of element on which we act. You can find out the types of possible elements using the following query:
SELECT distinct (class_desc) FROM sys.fn_builtin_permissions (default);
- permission : It is the permission you want to ask about.
If you need to know about any permission, and not about a specific one, there is the wildcard ‘ ANY ‘. - sub-securable : If the element can be divided into sub-elements, for example, if you want to know the permissions on columns of a table.
- sub-securable_class : The type or class of the subitem.
What value does the function return
When executing the query on the function, if the user has the permission, the function returns value 1, if it does not have the permission, it returns 0, and NULL if the permission or the type of object does not exist or is not valid.
Let’s see how to know if I have permissions in SQL Server with examples
We are going to see some examples of the different types of objects that we can have.
To find out if I have VIEW SERVER STATE permission on the current server, since permissions cannot be checked on linked servers:
SELECT HAS_PERMS_BY_NAME (null, null, 'VIEW SERVER STATE');
Know if I have permissions in the active DB:
SELECT HAS_PERMS_BY_NAME (db_name (), 'DATABASE', 'ANY');
Create procedures in the ‘dev’ schema:
SELECT HAS_PERMS_BY_NAME (db_name (), 'DATABASE', 'CREATE PROCEDURE')
& HAS_PERMS_BY_NAME ('dev', 'SCHEMA', 'ALTER') AS _can_create_procs;
It can also be combined with some system tables , for example to know on which tables I have SELECT permission:
SELECT HAS_PERMS_BY_NAME (QUOTENAME (SCHEMA_NAME (schema_id)) + '.' + QUOTENAME (name), 'OBJECT', 'SELECT') AS have_select, * FROM sys.tables;
And you can even know s on the columns of a specific table , in this case from the sales table:
SELECT name AS column_name,
HAS_PERMS_BY_NAME ('sales', 'OBJECT', 'SELECT', name, 'COLUMN')
AS can_select
FROM sys.columns AS c
WHERE c.object_id = object_id ('sales');
We hope it has been useful to you.
Greetings.
Database Team
If you don’t want to miss tricks like this and stay up to date with this world, subscribe to our newsletter . We will keep you informed with a single email per month.
Still do not know Query Performance ? Find out how it can help you in your Oracle environment. More information on their page LinkedIn .
Follow GPS on LinkedIn
If you have questions about its administration or related to Oracle or SQL Server, do not hesitate to contact us that we will be happy to help !!