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!
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.
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
As far as I know, you can achieve that with Row-Level Security. Available only in SQL Server 2016 though.