SQL Server – How to Control Server Order in SSMS Connection Dialog

The 2012 Books Online topic "Get Information About a View" states that the permissions required for this specific task are:

• VIEW DEFINITION permission on the database; and
• SELECT permission on sys.sql_expression_dependencies

Note that database-level VIEW DEFINITION is required to allow the user to see information in sys.sql_expression_dependencies; object-level VIEW DEFINITION and SELECT on the DMV will not work (you will receive an empty result set).

Example:

USE Sandpit;
GO
CREATE VIEW dbo.V1 AS SELECT 1 AS const;
GO
CREATE VIEW dbo.V2 AS SELECT v.const FROM dbo.V1 AS v;
GO
CREATE USER Bob WITHOUT LOGIN;

-- These two permissions are not sufficent
GRANT VIEW DEFINITION ON OBJECT::V1 TO Bob;
GRANT VIEW DEFINITION ON OBJECT::V2 TO Bob;

-- This one permission is enough (covers the two above)
GRANT VIEW DEFINITION ON DATABASE::Sandpit TO Bob;

-- Also required
GRANT SELECT ON OBJECT::sys.sql_expression_dependencies TO Bob;
GO
EXECUTE AS USER = 'Bob';
GO
SELECT OBJECT_DEFINITION(OBJECT_ID(N'V1', N'V'));
SELECT OBJECT_DEFINITION(OBJECT_ID(N'V2', N'V'));

-- Show DMV info for the test views
SELECT
    referencing_object_name = OBJECT_NAME(sed.referencing_id),
    sed.referencing_class_desc,
    sed.is_schema_bound_reference,
    sed.referenced_class_desc,
    sed.referenced_schema_name,
    sed.referenced_entity_name
FROM sys.sql_expression_dependencies AS sed
WHERE
    sed.referenced_id = OBJECT_ID(N'dbo.V1', N'V');
GO
REVERT;
GO
DROP USER Bob;
DROP VIEW dbo.V1, dbo.V2;

Regarding sp_depends: that requires only membership of the public role but is deprecated:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities instead.

Behavior 1 I can expect because there are some features that may or may not work as expected when using higher version of SSMS on lower version of instance. There could be things in the background that changed just slightly from 2014 to 2012 that you will probably never find any documentation on. I would actually create a connect item on this if you can recreate this scenario on another machine every time.

Now, Behavior 2 failed the first time I tried, but then succeeded every time after that, even after opening/closing windows. However if you are trying this using just PowerShell.exe it is to be expected that some things will not function the exact same way when you are dealing with the SQLPS provider. If you tried doing the same command within SQLPS.exe I would expect it will work every time, it does for me at least. Now I am not using SQL Server 2014, only 2012 is loaded on my machine right now.

You can see the fact of how different PowerShell.exe and SQLPS.exe are by comparing the assemblies loaded in each one: