Sql-server – Sql server 2012 grant role permission to just validate view but not select data from it

database-designsql serversql-server-2012t-sql

Is there a permission in SQL server 2012 to just validate/ existence an object (in my case it's a view) but not a select permission? Idea is if a usergroupA try select a view it just should not fail and return no records. Thanks in advance!

Best Answer

Is there a permission in SQL server 2012 to just validate/ existence an object (in my case it's a view) but not a select permission?

Consider granting VIEW DEFINITION permission as defined in the below quoted reference; this still applies to SQL Server 2012 as well.

VIEW DEFINITION

The VIEW DEFINITION permission lets a user see the metadata of the securable on which the permission is granted. However, VIEW DEFINITION permission does not confer access to the securable itself. For example, a user that is granted only VIEW DEFINITION permission on a table can see metadata related to the table in the sys.objects catalog view. However, without additional permissions such as SELECT or CONTROL, the user cannot read data from the table. For more information about viewing metadata, see GRANT (Transact-SQL).

Creating a per Database Role for VIEW DEFINITION permissions

/* CREATE A NEW ROLE  -- View Definition */
-- Database specific
CREATE ROLE db_All_ViewDefinition
GRANT VIEW DEFINITION TO db_All_ViewDefinition

Members of this role in the database you create it on should only have access to the applicable metadata as long as they don't have other permissions to database objects such as explicit SELECT access to the a table or a member of the db_datareader fixed database role.

Granting VIEW DEFINITION permissions explicitly per object

USE [DatabaseName];
GRANT VIEW DEFINITION ON dbo.tablename
TO [domain\username]

The above TSQL will grant explicit VIEW DEFINITION access to the specific DB object and to the specific security principal as specified in the applicable TSQL logic

Further Resources

Related Question