Sql-server – Login cannot view definition even when granted the permission

From BOL:

any permission denial overrides all grants

The implication is that when you DENY permissions to an object those permissions override any GRANTed permissions applicable to that object.

Having said that, you don't need to explicitly deny "view definition" permissions on procedures and other code. If you don't grant VIEW DEFINITION the definitions are not visible, even if the code can be executed. Tables work slightly differently; you'll need to deny view definition to any tables the role has SELECT rights on.

You can test this using the code below.

Create a test database:

USE master;
GO
IF EXISTS (SELECT 1 FROM master.sys.databases d WHERE d.name = 'TestPerms_20160921')
BEGIN
    DROP DATABASE TestPerms_20160921;
END;
GO
CREATE DATABASE TestPerms_20160921;
GO
USE TestPerms_20160921;
GO

Create a table, and a stored proc:

IF OBJECT_ID('dbo.Test') IS NOT NULL
DROP TABLE dbo.Test;
CREATE TABLE dbo.Test
(
    TestID INT NOT NULL
);
GO
IF OBJECT_ID('dbo.TestProc') IS NOT NULL
DROP PROCEDURE dbo.TestProc;
GO
CREATE PROCEDURE dbo.TestProc
AS
BEGIN
    SELECT 1;
END
GO

Create a test user, and a test role:

IF EXISTS (
    SELECT 1 
    FROM sys.database_principals dp 
    WHERE dp.name = 'TestU'
    )
BEGIN
    DROP USER TestU;
END
CREATE USER TestU WITHOUT LOGIN;

IF EXISTS (
    SELECT * 
    FROM sys.database_principals dp 
    WHERE dp.name = 'dbtestrole'
    )
BEGIN
    DROP ROLE dbtestrole;
END
CREATE ROLE dbtestrole;

ALTER ROLE dbtestrole ADD MEMBER TestU;

Grant SELECT and EXECUTE to the test objects:

GRANT SELECT ON dbo.Test TO dbtestrole;
GRANT EXECUTE ON dbo.TestProc TO dbtestrole;

Impersonate the user so we can see the effect of the GRANT statements above:

EXECUTE AS USER = 'TestU';

/* column names and types **are** visible */    
SELECT s.name
    , t.name
    , c.name
    , ty.name
FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN sys.columns c ON t.object_id = c.object_id
    INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE s.name = 'dbo'
    AND t.name = 'Test'
ORDER BY s.name
    , t.name
    , c.name;

/* access to view the definition is denied */
EXEC sys.sp_helptext @objname = 'dbo.TestProc', @columnname = NULL;

/* we can run the proc! */
EXEC dbo.TestProc;

REVERT;

DENY VIEW DEFINITION on the table, and test again:

DENY VIEW DEFINITION ON dbo.Test TO dbtestrole;

EXECUTE AS USER = 'TestU';

/* no rows returned as we can no longer view the table definition */
SELECT s.name
    , t.name
    , c.name
    , ty.name
FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN sys.columns c ON t.object_id = c.object_id
    INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE s.name = 'dbo'
    AND t.name = 'Test'
ORDER BY s.name
    , t.name
    , c.name;

/* we can still run the proc */
EXEC dbo.TestProc;

/* we can still see the content of the table */
SELECT *
FROM dbo.Test;

REVERT;

Cleanup:

USE master;
GO
IF EXISTS (SELECT 1 FROM master.sys.databases d WHERE d.name = 'TestPerms_20160921')
DROP DATABASE TestPerms_20160921;

Not sure where you've stumbled along the way, but this works for me:

CREATE LOGIN permtest WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
USE somedatabase;
GO
CREATE USER permtest FROM LOGIN permtest;
GO

According to this page, the user needs SELECT permission on sys.sql_expression_dependencies, and VIEW DEFINITION on the database.

In my experimentation, the following allowed the user to select from the view, but it returned 0 rows, because they don't have the ability to view definition (which includes dependency chains):

GRANT SELECT ON sys.sql_expression_dependencies TO permtest;

In order to actually see any relationships in somedatabase, I also had to add the following:

GRANT VIEW DEFINITION ON DATABASE::floob TO permtest;

I could not find any way to make that more granular (VIEW/DENY definition worked for individual objects, but without the database-level right, I still couldn't see any rows in the catalog view, and DENY did not prevent the objects from showing up in the catalog view nor did it even prevent me from viewing the definition). I feel like SQL Server would have a hard time resolving that granularity anyway - if you had a view that referenced a table, how should the catalog view look if you have grant on the view and deny on the table, or vice versa?

If you don't want to grant VIEW DEFINITION on the database, then create procedures that use EXECUTE AS OWNER, select (filtered?) rows from the catalog view, and give the users (and of course, that could also be a role) execute permissions on the procedure.

CREATE PROCEDURE dbo.GetDependencies
WITH EXECUTE AS OWNER
AS
BEGIN
  SELECT is_schema_bound_reference --, ...
    FROM sys.sql_expression_dependencies;
END
GO

GRANT EXECUTE ON dbo.GetDependencies TO permtest;