Can SQL Server disable IntelliSense on SSMS?

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 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;

edited Nov 20 '18 at 18:55

Max Vernon

49.8k13111219

asked Nov 20 '18 at 16:16

Farhan

1214

2

What permissions do you have on the instance and database(s)? This may be permission related depending on your security setup. I say this as I've seen environments that revoke default grants to public which can exhibit this behavior.
– John Eisbrener
Nov 20 '18 at 16:19

@JohnEisbrener Very little permission. I've to find out. However, I can select and update records in the database I'm working in, but didn't try insert.
– Farhan
Nov 20 '18 at 16:40

add a comment |

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 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;

edited Nov 20 '18 at 18:55

Max Vernon

49.8k13111219

asked Nov 20 '18 at 16:16

Farhan

1214

2

What permissions do you have on the instance and database(s)? This may be permission related depending on your security setup. I say this as I've seen environments that revoke default grants to public which can exhibit this behavior.
– John Eisbrener
Nov 20 '18 at 16:19

@JohnEisbrener Very little permission. I've to find out. However, I can select and update records in the database I'm working in, but didn't try insert.
– Farhan
Nov 20 '18 at 16:40

add a comment |

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 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;

edited Nov 20 '18 at 18:55

Max Vernon

49.8k13111219

asked Nov 20 '18 at 16:16

Farhan

1214

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 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;

sql-server sql-server-2016 ssms intellisense

edited Nov 20 '18 at 18:55

Max Vernon

49.8k13111219

asked Nov 20 '18 at 16:16

Farhan

1214

edited Nov 20 '18 at 18:55

Max Vernon

49.8k13111219

asked Nov 20 '18 at 16:16

Farhan

1214

edited Nov 20 '18 at 18:55

Max Vernon

49.8k13111219

edited Nov 20 '18 at 18:55

Max Vernon

49.8k13111219

edited Nov 20 '18 at 18:55

Max Vernon

49.8k13111219

asked Nov 20 '18 at 16:16

Farhan

1214

asked Nov 20 '18 at 16:16

Farhan

1214

asked Nov 20 '18 at 16:16

Farhan

1214

2

What permissions do you have on the instance and database(s)? This may be permission related depending on your security setup. I say this as I've seen environments that revoke default grants to public which can exhibit this behavior.
– John Eisbrener
Nov 20 '18 at 16:19

@JohnEisbrener Very little permission. I've to find out. However, I can select and update records in the database I'm working in, but didn't try insert.
– Farhan
Nov 20 '18 at 16:40

add a comment |

2

What permissions do you have on the instance and database(s)? This may be permission related depending on your security setup. I say this as I've seen environments that revoke default grants to public which can exhibit this behavior.
– John Eisbrener
Nov 20 '18 at 16:19

@JohnEisbrener Very little permission. I've to find out. However, I can select and update records in the database I'm working in, but didn't try insert.
– Farhan
Nov 20 '18 at 16:40

What permissions do you have on the instance and database(s)? This may be permission related depending on your security setup. I say this as I've seen environments that revoke default grants to public which can exhibit this behavior.
– John Eisbrener
Nov 20 '18 at 16:19

@JohnEisbrener Very little permission. I've to find out. However, I can select and update records in the database I'm working in, but didn't try insert.
– Farhan
Nov 20 '18 at 16:40

add a comment |

1 Answer
1

active

oldest

votes

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

Completion lists do not include database objects for which you do not have permissions. IntelliSense flags references to objects for which you do have permissions. For example, if you open a script that is written by someone else, any references to objects for which that person has permissions and you do not are flagged as incorrect.

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:

USE [master]

GO

CREATE LOGIN [nerd-login] WITH PASSWORD = 'NerdPassword1';





USE [SomeDatabase]

GO

CREATE USER [nerd-user] FOR LOGIN [nerd-login] WITH DEFAULT_SCHEMA=[dbo]

GO

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:

USE [SomeDatabase]

GO

GRANT VIEW DEFINITION TO [nerd-user];

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 the master database context), or at the per-object level.

Latency

In the comments here, you've mentioned:

Server is about 50 miles away, but I do feel there is some issue with network/computer because opening a new browser takes several seconds.

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

And finally, you may just need to be patient. Check your network speed, the general health of the server, and be understanding if your metadata is quite large - if you are running SAP, for example, there's a lot more data to bring across than AdventureWorks.

When SSMS is attempting to get metadata from the destination server, it may time out before it starts receiving results. This timeout is hard-coded to two seconds in SSMS 2012, but in previous versions, it would wait for the database connection to time out (which could be 30 seconds or more). It will continue trying in the background, so you may not see the drop-down appear right away, but it could appear after successive attempts at pressing Ctrl+J.

You can force it to try to reload by pressing Ctrl+Shift+R or the menu option Edit > IntelliSense > Refresh Local Cache. Once metadata is retrieved in the background, the data is cached in local memory. If the connection is working and none of the above factors are in play, the cache will eventually be populated and you should be able to use all of the functionality.

But again, on SSMS 2008 or SSMS 2008 R2, this may take a little bit longer because of the more relaxed timeout.

Note: I realize that's about SSMS 2012, but the same general principles still apply

edited Nov 21 '18 at 16:34

answered Nov 20 '18 at 18:39

jadarnel27

3,6501330

Thanks for your answer. Does the permission also cause intellisense on function names (e.g. getdate()) and variable declarations (e.g. declare @myName varchar(25)) to stop working?
– Farhan
Nov 21 '18 at 14:20

@Farhan I don't think it would affect that. The primary cause for that kind of intellisense issue, I think, would be latency communicating with the SQL Server database engine. Are you getting red squiggly lines on GETDATE(), or just not seeing it in completion lists? Is the server you're connected to very far away, or on a slow network, or across a slow VPN, etc?
– jadarnel27
Nov 21 '18 at 15:26

No, there is no red squiggly line under getdate(), in fact the color changes as soon as I type the last e, indicating that SSMS knows it's a function. When intellisense works, you would see the locally declared variable names popup too, but that's not happening either. Server is about 50 miles away, but I do feel there is some issue with network/computer because opening a new browser takes several seconds.
– Farhan
Nov 21 '18 at 16:08

@Farhan okay, that sounds more like latency, or the server is too busy to respond to the metadata queries in a timely manner, etc. I've updated the answer. In that case, you should eventually be able to get intellisense. It just might take a while.
– jadarnel27
Nov 21 '18 at 16:40

add a comment |

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f222992%2fcan-sql-server-disable-intellisense-on-ssms%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

Permissions

Troubleshooting IntelliSense -> Database Engine Query IntelliSense

Completion lists do not include database objects for which you do not have permissions. IntelliSense flags references to objects for which you do have permissions. For example, if you open a script that is written by someone else, any references to objects for which that person has permissions and you do not are flagged as incorrect.

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:

USE [master]

GO

CREATE LOGIN [nerd-login] WITH PASSWORD = 'NerdPassword1';





USE [SomeDatabase]

GO

CREATE USER [nerd-user] FOR LOGIN [nerd-login] WITH DEFAULT_SCHEMA=[dbo]

GO

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:

USE [SomeDatabase]

GO

GRANT VIEW DEFINITION TO [nerd-user];

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 the master database context), or at the per-object level.

Latency

In the comments here, you've mentioned:

Server is about 50 miles away, but I do feel there is some issue with network/computer because opening a new browser takes several seconds.

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

And finally, you may just need to be patient. Check your network speed, the general health of the server, and be understanding if your metadata is quite large - if you are running SAP, for example, there's a lot more data to bring across than AdventureWorks.

When SSMS is attempting to get metadata from the destination server, it may time out before it starts receiving results. This timeout is hard-coded to two seconds in SSMS 2012, but in previous versions, it would wait for the database connection to time out (which could be 30 seconds or more). It will continue trying in the background, so you may not see the drop-down appear right away, but it could appear after successive attempts at pressing Ctrl+J.

You can force it to try to reload by pressing Ctrl+Shift+R or the menu option Edit > IntelliSense > Refresh Local Cache. Once metadata is retrieved in the background, the data is cached in local memory. If the connection is working and none of the above factors are in play, the cache will eventually be populated and you should be able to use all of the functionality.

But again, on SSMS 2008 or SSMS 2008 R2, this may take a little bit longer because of the more relaxed timeout.

Note: I realize that's about SSMS 2012, but the same general principles still apply

edited Nov 21 '18 at 16:34

answered Nov 20 '18 at 18:39

jadarnel27

3,6501330

Thanks for your answer. Does the permission also cause intellisense on function names (e.g. getdate()) and variable declarations (e.g. declare @myName varchar(25)) to stop working?
– Farhan
Nov 21 '18 at 14:20

@Farhan I don't think it would affect that. The primary cause for that kind of intellisense issue, I think, would be latency communicating with the SQL Server database engine. Are you getting red squiggly lines on GETDATE(), or just not seeing it in completion lists? Is the server you're connected to very far away, or on a slow network, or across a slow VPN, etc?
– jadarnel27
Nov 21 '18 at 15:26

No, there is no red squiggly line under getdate(), in fact the color changes as soon as I type the last e, indicating that SSMS knows it's a function. When intellisense works, you would see the locally declared variable names popup too, but that's not happening either. Server is about 50 miles away, but I do feel there is some issue with network/computer because opening a new browser takes several seconds.
– Farhan
Nov 21 '18 at 16:08

@Farhan okay, that sounds more like latency, or the server is too busy to respond to the metadata queries in a timely manner, etc. I've updated the answer. In that case, you should eventually be able to get intellisense. It just might take a while.
– jadarnel27
Nov 21 '18 at 16:40

add a comment |

Permissions

Troubleshooting IntelliSense -> Database Engine Query IntelliSense

Completion lists do not include database objects for which you do not have permissions. IntelliSense flags references to objects for which you do have permissions. For example, if you open a script that is written by someone else, any references to objects for which that person has permissions and you do not are flagged as incorrect.

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:

USE [master]

GO

CREATE LOGIN [nerd-login] WITH PASSWORD = 'NerdPassword1';





USE [SomeDatabase]

GO

CREATE USER [nerd-user] FOR LOGIN [nerd-login] WITH DEFAULT_SCHEMA=[dbo]

GO

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:

USE [SomeDatabase]

GO

GRANT VIEW DEFINITION TO [nerd-user];

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 the master database context), or at the per-object level.

Latency

In the comments here, you've mentioned:

Server is about 50 miles away, but I do feel there is some issue with network/computer because opening a new browser takes several seconds.

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

And finally, you may just need to be patient. Check your network speed, the general health of the server, and be understanding if your metadata is quite large - if you are running SAP, for example, there's a lot more data to bring across than AdventureWorks.

When SSMS is attempting to get metadata from the destination server, it may time out before it starts receiving results. This timeout is hard-coded to two seconds in SSMS 2012, but in previous versions, it would wait for the database connection to time out (which could be 30 seconds or more). It will continue trying in the background, so you may not see the drop-down appear right away, but it could appear after successive attempts at pressing Ctrl+J.

You can force it to try to reload by pressing Ctrl+Shift+R or the menu option Edit > IntelliSense > Refresh Local Cache. Once metadata is retrieved in the background, the data is cached in local memory. If the connection is working and none of the above factors are in play, the cache will eventually be populated and you should be able to use all of the functionality.

But again, on SSMS 2008 or SSMS 2008 R2, this may take a little bit longer because of the more relaxed timeout.

Note: I realize that's about SSMS 2012, but the same general principles still apply

edited Nov 21 '18 at 16:34

answered Nov 20 '18 at 18:39

jadarnel27

3,6501330

Thanks for your answer. Does the permission also cause intellisense on function names (e.g. getdate()) and variable declarations (e.g. declare @myName varchar(25)) to stop working?
– Farhan
Nov 21 '18 at 14:20

@Farhan I don't think it would affect that. The primary cause for that kind of intellisense issue, I think, would be latency communicating with the SQL Server database engine. Are you getting red squiggly lines on GETDATE(), or just not seeing it in completion lists? Is the server you're connected to very far away, or on a slow network, or across a slow VPN, etc?
– jadarnel27
Nov 21 '18 at 15:26

No, there is no red squiggly line under getdate(), in fact the color changes as soon as I type the last e, indicating that SSMS knows it's a function. When intellisense works, you would see the locally declared variable names popup too, but that's not happening either. Server is about 50 miles away, but I do feel there is some issue with network/computer because opening a new browser takes several seconds.
– Farhan
Nov 21 '18 at 16:08

@Farhan okay, that sounds more like latency, or the server is too busy to respond to the metadata queries in a timely manner, etc. I've updated the answer. In that case, you should eventually be able to get intellisense. It just might take a while.
– jadarnel27
Nov 21 '18 at 16:40

add a comment |

Permissions

Troubleshooting IntelliSense -> Database Engine Query IntelliSense

Completion lists do not include database objects for which you do not have permissions. IntelliSense flags references to objects for which you do have permissions. For example, if you open a script that is written by someone else, any references to objects for which that person has permissions and you do not are flagged as incorrect.

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:

USE [master]

GO

CREATE LOGIN [nerd-login] WITH PASSWORD = 'NerdPassword1';





USE [SomeDatabase]

GO

CREATE USER [nerd-user] FOR LOGIN [nerd-login] WITH DEFAULT_SCHEMA=[dbo]

GO

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:

USE [SomeDatabase]

GO

GRANT VIEW DEFINITION TO [nerd-user];

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 the master database context), or at the per-object level.

Latency

In the comments here, you've mentioned:

Server is about 50 miles away, but I do feel there is some issue with network/computer because opening a new browser takes several seconds.

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

And finally, you may just need to be patient. Check your network speed, the general health of the server, and be understanding if your metadata is quite large - if you are running SAP, for example, there's a lot more data to bring across than AdventureWorks.

When SSMS is attempting to get metadata from the destination server, it may time out before it starts receiving results. This timeout is hard-coded to two seconds in SSMS 2012, but in previous versions, it would wait for the database connection to time out (which could be 30 seconds or more). It will continue trying in the background, so you may not see the drop-down appear right away, but it could appear after successive attempts at pressing Ctrl+J.

You can force it to try to reload by pressing Ctrl+Shift+R or the menu option Edit > IntelliSense > Refresh Local Cache. Once metadata is retrieved in the background, the data is cached in local memory. If the connection is working and none of the above factors are in play, the cache will eventually be populated and you should be able to use all of the functionality.

But again, on SSMS 2008 or SSMS 2008 R2, this may take a little bit longer because of the more relaxed timeout.

Note: I realize that's about SSMS 2012, but the same general principles still apply

edited Nov 21 '18 at 16:34

answered Nov 20 '18 at 18:39

jadarnel27

3,6501330

Permissions

Troubleshooting IntelliSense -> Database Engine Query IntelliSense

Completion lists do not include database objects for which you do not have permissions. IntelliSense flags references to objects for which you do have permissions. For example, if you open a script that is written by someone else, any references to objects for which that person has permissions and you do not are flagged as incorrect.

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:

USE [master]

GO

CREATE LOGIN [nerd-login] WITH PASSWORD = 'NerdPassword1';





USE [SomeDatabase]

GO

CREATE USER [nerd-user] FOR LOGIN [nerd-login] WITH DEFAULT_SCHEMA=[dbo]

GO

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:

USE [SomeDatabase]

GO

GRANT VIEW DEFINITION TO [nerd-user];

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 the master database context), or at the per-object level.

Latency

In the comments here, you've mentioned:

Server is about 50 miles away, but I do feel there is some issue with network/computer because opening a new browser takes several seconds.

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

And finally, you may just need to be patient. Check your network speed, the general health of the server, and be understanding if your metadata is quite large - if you are running SAP, for example, there's a lot more data to bring across than AdventureWorks.

When SSMS is attempting to get metadata from the destination server, it may time out before it starts receiving results. This timeout is hard-coded to two seconds in SSMS 2012, but in previous versions, it would wait for the database connection to time out (which could be 30 seconds or more). It will continue trying in the background, so you may not see the drop-down appear right away, but it could appear after successive attempts at pressing Ctrl+J.

You can force it to try to reload by pressing Ctrl+Shift+R or the menu option Edit > IntelliSense > Refresh Local Cache. Once metadata is retrieved in the background, the data is cached in local memory. If the connection is working and none of the above factors are in play, the cache will eventually be populated and you should be able to use all of the functionality.

But again, on SSMS 2008 or SSMS 2008 R2, this may take a little bit longer because of the more relaxed timeout.

Note: I realize that's about SSMS 2012, but the same general principles still apply

edited Nov 21 '18 at 16:34

answered Nov 20 '18 at 18:39

jadarnel27

3,6501330

edited Nov 21 '18 at 16:34

answered Nov 20 '18 at 18:39

jadarnel27

3,6501330

answered Nov 20 '18 at 18:39

jadarnel27

3,6501330

answered Nov 20 '18 at 18:39

jadarnel27

3,6501330

Thanks for your answer. Does the permission also cause intellisense on function names (e.g. getdate()) and variable declarations (e.g. declare @myName varchar(25)) to stop working?
– Farhan
Nov 21 '18 at 14:20

@Farhan I don't think it would affect that. The primary cause for that kind of intellisense issue, I think, would be latency communicating with the SQL Server database engine. Are you getting red squiggly lines on GETDATE(), or just not seeing it in completion lists? Is the server you're connected to very far away, or on a slow network, or across a slow VPN, etc?
– jadarnel27
Nov 21 '18 at 15:26

No, there is no red squiggly line under getdate(), in fact the color changes as soon as I type the last e, indicating that SSMS knows it's a function. When intellisense works, you would see the locally declared variable names popup too, but that's not happening either. Server is about 50 miles away, but I do feel there is some issue with network/computer because opening a new browser takes several seconds.
– Farhan
Nov 21 '18 at 16:08

@Farhan okay, that sounds more like latency, or the server is too busy to respond to the metadata queries in a timely manner, etc. I've updated the answer. In that case, you should eventually be able to get intellisense. It just might take a while.
– jadarnel27
Nov 21 '18 at 16:40

add a comment |

Thanks for your answer. Does the permission also cause intellisense on function names (e.g. getdate()) and variable declarations (e.g. declare @myName varchar(25)) to stop working?
– Farhan
Nov 21 '18 at 14:20

@Farhan I don't think it would affect that. The primary cause for that kind of intellisense issue, I think, would be latency communicating with the SQL Server database engine. Are you getting red squiggly lines on GETDATE(), or just not seeing it in completion lists? Is the server you're connected to very far away, or on a slow network, or across a slow VPN, etc?
– jadarnel27
Nov 21 '18 at 15:26

No, there is no red squiggly line under getdate(), in fact the color changes as soon as I type the last e, indicating that SSMS knows it's a function. When intellisense works, you would see the locally declared variable names popup too, but that's not happening either. Server is about 50 miles away, but I do feel there is some issue with network/computer because opening a new browser takes several seconds.
– Farhan
Nov 21 '18 at 16:08

@Farhan okay, that sounds more like latency, or the server is too busy to respond to the metadata queries in a timely manner, etc. I've updated the answer. In that case, you should eventually be able to get intellisense. It just might take a while.
– jadarnel27
Nov 21 '18 at 16:40

Thanks for your answer. Does the permission also cause intellisense on function names (e.g. getdate()) and variable declarations (e.g. declare @myName varchar(25)) to stop working?
– Farhan
Nov 21 '18 at 14:20

@Farhan I don't think it would affect that. The primary cause for that kind of intellisense issue, I think, would be latency communicating with the SQL Server database engine. Are you getting red squiggly lines on GETDATE(), or just not seeing it in completion lists? Is the server you're connected to very far away, or on a slow network, or across a slow VPN, etc?
– jadarnel27
Nov 21 '18 at 15:26

No, there is no red squiggly line under getdate(), in fact the color changes as soon as I type the last e, indicating that SSMS knows it's a function. When intellisense works, you would see the locally declared variable names popup too, but that's not happening either. Server is about 50 miles away, but I do feel there is some issue with network/computer because opening a new browser takes several seconds.
– Farhan
Nov 21 '18 at 16:08

@Farhan okay, that sounds more like latency, or the server is too busy to respond to the metadata queries in a timely manner, etc. I've updated the answer. In that case, you should eventually be able to get intellisense. It just might take a while.
– jadarnel27
Nov 21 '18 at 16:40

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Database Administrators Stack Exchange!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu