March 1, 2013 at 2:50 am
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
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
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
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
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy