First published on MSDN on Apr 12, 2018
The answer is Yes! but we need to do some additional steps in our environment.
If you are the SQL Login administrator or Azure Active Directory administrator user you will have the list of the objects using Intellisense, but, what happens if I'm connecting with non-adminstrator Azure Active Directory user. Let me show you what happens with an example:
STEP 1: I have configured the following scenario.
STEP 2: I created a SQL login called ReaderUser100 and user ReadUser100 in DB1 with minimal permissions for Intellisense.
CREATE LOGIN ReaderUser100 WITH PASSWORD=’….’
CREATE USER ReaderUser100 FOR LOGIN ReaderUser100
CREATE USER ReaderUser100 FOR LOGIN ReaderUser100
alter role db_datareader add member ReaderUser100
STEP 3: Once I connected to DB1 database and after updating the list of the objects, I’m not able to obtain the list, because, eventhought after refreshing the local cache of Intellisense using Edit->Intellisense->Refresh local cache, I saw that Intellisense process is not able to connect to the second database called DB2 (using SQL Auditing), giving a silence error (no error message to the user).
STEP 4: However, if I add the user ReadUser100 in DB2 with reading permissions and after refreshing the local cache of intellisense, I’m able to see all the objects.
STEP 5: I realized that every time that SQL Server Management Studio requests the list of the objects:
-
Connects using the credentials of the user connected.
-
Using SQL Auditing the application name that is connect has the name: Microsoft SQL Server Management Studio - Transact-SQL IntelliSense.
STEP 6: I was able to reproduce the issue using Azure Active Directory and SQL Login:
So, the Azure Active Directory users are contained users: All tests that I performed pointed to that Intellisense needs access to master database level and all databases included in the server, for this reason, a contained user is not able to.
In summary, what do I need to obtain the list of the members using an Azure Active Directory non-administrator user:
-
User with permission in all databases that you have in your server, at least, with reading permissions.
-
Needs to be needed to create the user in master too without permissions, just only create it.
-
After these two process, if you refresh the local cache of intellisense of SQL Server Management Studio you might be able to see the list of the members of the database connected.
