Lesson Learned #6: Working with HAS_PERMS_BY_NAME function and Database Name with dots

  • Article

There is very common that the name of the database could contains a dot in its name. In this situation, we need to be aware of how this could impact using HAS_PERMS_BY_NAME function. For example, we have a situation where a customer is executing this query where the database name is My.Database, they want to know if an user has access to this database to create a table, running the following TSQL:SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'CREATE TABLE'). Unfortunately, although the user has permissions to create table or sysadmin permissions, the function returned 0, that means no access.

 

Analyzing the situation, I noticed that this issue is possible to reproduce in SQL SERVER 2016 and SQL SERVER 2014 OnPremise. When you execute the call SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'CREATE TABLE') the following things are evaluated

 

1. The first parameter db_name() is splitting in 4 different entities. This is the way that we could make a reference an object in SQL SERVER (Server.Database.Schema.Object) in OnPremise.

 

2. In this situation, even when you specify DATABASE as parameter to check, we are splitting as a server name the first part of this value before the dot and database name starting the dot to the end of the text.

 

3.For this reason, if our customer database name is “My.Database” you are able to obtain the correct value of the permission using one of these different combinations:

3.1.Removing the dot and renaming the database without dot.

3.2.Adding the brackets  [] at the beginning and at the end, for example, [My.Database]

3.3.Leaving this value as null, for example, SELECT HAS_PERMS_BY_NAME(Null, 'DATABASE', 'CREATE TABLE').