2

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!

  • Since you're a new member I wanted to make sure you knew about pressing the little check mark on the answer which resolves your question just to the left of it to tag it as the accepted answer . . . see Accepting An Answer for more detail on this if needed. – Pimp Juice IT Aug 28 '16 at 21:28
1

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

  • Will this allow the user to select from the view and return no rows? – Dan Guzman Aug 28 '16 at 22:33
  • @DanGuzman There's an option [Granting VIEW DEFINITION permissions explicitly per object] listed in my answer to assign the explicit VIEW DEFINITION permission on a per-DB-object basis. Without over analyzing this simple question too much, word-for-word for the part of the OP's question which I answered, and quoted in the top of my answer, this is what I would consider, test, confirm, and all other relevant aspects to ensure it works as expected before rolling out into a production environment. – Pimp Juice IT Aug 28 '16 at 23:44
  • Granting View Definition explicitly per object doesn't allow user to select from the view and return no rows. Is there any other way we can achieve this? – user6762139 Aug 30 '16 at 8:45
0

As far as I know, you can achieve that with Row-Level Security. Available only in SQL Server 2016 though.

  • Have you found another way to do that? View definition permission just give you the ability to see the structure of the object. – Danilo Braga Aug 31 '16 at 3:50
  • Unfortunately no Dan there's no inbuilt feature as you said up until 2016 but came up with a work around to altered view to have a clause which filters out what users can see based on group membership using the IS_MEMBER function. – user6762139 Sep 15 '16 at 8:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.