SSMS : IntelliSense does not function for a contained user by aaronbertrand

Active

Type:	Bug
ID:	717063
Opened:	1/6/2012 6:00:59 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

It appears that IntelliSense is not fully functional for a contained user (a database user with a password, in a database marked as containment = partial). This may be by design or a known issue, but it certainly surprised me.

1. create a contained database
2. create a user with password in the contained database
3. connect to the contained database as the contained user

Underlining works for bad syntax, but not for invalid objects. Auto-complete lists do not appear, even after issuing Ctrl+Shift+R and issuing Ctrl+J as many times as you feel comfortable. And even tooltip help for built-in functions like DATEADD() does not work. This includes after creating schema with authorization set to the contained user, granting explicit permissions on dbo, sys, etc.

At first I thought it was a symptom of contained databases, but all of this functionality works fine if I use a non-contained user against the same database.

Now, I'm not upset if this is the case, but I think some users out there might be, and I think if this is how it is going to work for RTM, it should at the very least be documented. However I can't imagine this is intentional; there must be something about a contained user that is preventing metadata access.

Details (expand)

English

SQL Server 2012 RC0

Tools (SSMS, Agent, Profiler, Migration, etc.)

Windows 7 (all editions) (SP1)

US English

USE [master];
GO
EXEC sp_configure 'contained database authentication', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
CREATE DATABASE IntelliSense;
GO
ALTER DATABASE IntelliSense SET CONTAINMENT = PARTIAL;
GO
USE IntelliSense;
GO
CREATE USER foo WITH PASSWORD = 'bar'; -- you may need more complex pw for policy
GO

Now connect to the database in a new query window with the foo/bar credentials and database set to IntelliSense on the Options tab.

Unlike in the main query window with your normal login, IntelliSense autocomplete, Ctrl+J, tooltips on functions like DATEADD(), and underlining for invalid columns/objects do not function. However you will see that incorrect syntax gets underlined, e.g.:

SELCT * FROM dbo.doesnotexist;
~

If you correct the syntax, you get no underlining:

SELECT * FROM dbo.doesnotexist;

And if you try to use Ctrl+J (list members) in place of the * or at dbo., nothing happens. You can also try:

SELECT DATEADD(

In your main query window, when your cursor is after the parenthesis, you will see a tooltip describing the interface for the built-in function. When in foo's query window, nothing happens.

Go ahead and create new objects, even new schemas, mess with all kinds of permissions on those objects for foo, IntelliSense will not function in any of the cases that I have tried. The above is the simplest. You can also try after adding foo the db_owner role:

EXEC sp_addrolemember 'db_owner', 'foo';

Results here are the same.

IntelliSense should function the same for a contained user as a non-contained user.

X64

0 attachments

Posted by deBett on 3/21/2012 at 9:47 AM

I have the same issue. Would really want it to be addressed.

Microsoft