I am using SSMS 2016 (ver: 13.0.15000.23) and connecting to SQL Server database 2016 (ver: 13.0.5081.1).
IntelliSense is not working on my SSMS.
I tried the following:
- Tools >> Options >> Text Editor >> Transact-SQL >> IntelliSense, and select Enable IntelliSense
- Tools >> Options >> Text Editor >> Transact-SQL >> General, and verify Auto list members and Parameter information check boxes are checked
- Query >> IntelliSense Enabled
- Verify that SQLCMD is disabled
- Refreshed IntelliSense cache (Ctrl+Shift+R)
- Making sure no compile errors are in the query
- Uninstall and reinstall SSMS
I asked other members in my team and IntelliSense only works sporadically for them, if it does.
Question:
Is there a server setting which is disabling IntelliSense on SSMS?
I've already gone through all of these questions:
- IntelliSense not working but it is enabled
- IntelliSense is not working at SQL Server Management Studio
- Why is SQL Server 2008 Management Studio Intellisense not working?
- SQL Server Intellisense not working on some servers
- SQL Server 2008 R2 intellisense doesn't work
- SSMS IntelliSense doesn't work on particular database
I have limited permissions on the instance, but I can select and update records in the database I'm working in (I didn't try insert). I can see around 1,600 names of objects and columns by running:
SELECT o.name, c.name
FROM sys.objects o
INNER JOIN sys.columns c ON o.object_id = c.object_id
WHERE o.is_ms_shipped = 0;
Best Answer
Permissions
As the comment answers you've received so far imply, the most likely reason for this problem is that you don't have the appropriate permissions to that database. This is (rather vaguely) documented here:
Troubleshooting IntelliSense -> Database Engine Query IntelliSense
The easiest way to fix this is to have a DBA grant the "View Definition" privilege to the database user that you're using to connect to this database.
For example, I can create this login, and a database user for a specific database, on my local SQL Server 2016 instance:
And if I login as nerd-login, I can connect to the
SomeDatabase
database, but I can't get any intellisense on it. But, after running this command:I'm able to get intellisense on the objects in the
SomeDatabase
database.This can also be granted more broadly at the instance level (by executing
GRANT VIEW ANY DEFINITION
in themaster
database context), or at the per-object level.Latency
In the comments here, you've mentioned:
Factors like the amount of metadata in a particular database (# of tables, views, columns, functions, stored procedures, etc), the network speed between you and the database, and how busy the other database is, can all affect intellisense.
Here's a pretty thorough treatment of the latency issue in a blog post from Aaron Bertrand:
Troubleshooting IntelliSense in SQL Server Management Studio 2012
Note: I realize that's about SSMS 2012, but the same general principles still apply