Post reply

GRANT VIEW DEFINITION

  • March 1, 2013 at 2:50 am

    #274731

    Hi all

    I am running the following on one of my DBs - I am using SSMS 2012 against SQL 2008R2

    GRANT VIEW Definition TO [XXX\XXX]

    When I check the securables for that user it doesn't list anything as having being granted and if I run sp_helpprotect there is nothing in there either. The command executes successfully.

    Any ideas?

    Thanks

  • March 1, 2013 at 4:16 am

    #1592307

    Hi, the securables list shows objects by default - you need to search for database-level permissions.

    (Search... All objects of the types... select database.. ok)

    sp_helpprotect doesn't show information for all securables - but you should see the permission listed in sys.database_permissions.

    Cheers

    Gaz

  • March 4, 2013 at 3:24 am

    #1592933

    Hi just to clarify in order to get this working I had to grant each object view definition explicitly. I noticed the DB was in SQL 2005 compatibility mode so I am unsure if this was why.

  • March 4, 2013 at 4:13 am

    #1592942

    Interesting, it should be able to be granted at the database or server scope!

    Don't think compatibility level should affect it, and the VIEW DEFINITION securable was introduced with SQL 2005 in any case (which is why it doesn't show in sp_helpprotect).

    Cheers

  • March 4, 2013 at 5:29 am

    #1592964

    Yes there was nothing in sys.database_permissions for that role. I know it shouldn't make a difference but using SSMS 2012 to connect to a SQL 2008 instance with a DB in 2005 compatibility mode might have been it, I haven't tested it though.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply