December 13, 2017 at 12:39 pm
Hello All,
We are implementing some Compliance Mechanisms on our Production SQL Instances, one of which is that Developers can no longer have Read access; however, there is no reason they shouldn't be able to view the Table Columns, Views, STP definitions, etc... So for the Development group, I removed all DB level Users mapped to the Development Group Login, then removed the Development Group Login. We then traversed to the Public Role at the Instance Level and granted View Definition and Connect Any Database.
The problem is when a Developer uses Object Explorer to try and view the Tables, Views, STPs, etc.., they receive a message like the one below (message differs slightly depending on what they are trying to access, tables, views, etc..). Am I doing something wrong or thinking of the hierarchy incorrectly? How can I accomplish cleanly to grant a group (or Public role) the ability to view the contents of STPs, Table Columns, etc... without giving them SELECT access to the data? Please let me know if I've left out any pertinent information.
--Error--
The SELECT permission was denied on the object 'types', database 'mssqlsystemresource', schema 'sys'. (.Net SqlClient Data Provider)
Thanks,
Chris
December 13, 2017 at 2:01 pm
chris_barnhart - Wednesday, December 13, 2017 12:39 PMHello All,
We are implementing some Compliance Mechanisms on our Production SQL Instances, one of which is that Developers can no longer have Read access; however, there is no reason they shouldn't be able to view the Table Columns, Views, STP definitions, etc... So for the Development group, I removed all DB level Users mapped to the Development Group Login, then removed the Development Group Login. We then traversed to the Public Role at the Instance Level and granted View Definition and Connect Any Database.The problem is when a Developer uses Object Explorer to try and view the Tables, Views, STPs, etc.., they receive a message like the one below (message differs slightly depending on what they are trying to access, tables, views, etc..). Am I doing something wrong or thinking of the hierarchy incorrectly? How can I accomplish cleanly to grant a group (or Public role) the ability to view the contents of STPs, Table Columns, etc... without giving them SELECT access to the data? Please let me know if I've left out any pertinent information.
--Error--
The SELECT permission was denied on the object 'types', database 'mssqlsystemresource', schema 'sys'. (.Net SqlClient Data Provider)Thanks,
Chris
That error looks a bit like the developers have been denied something or are in one of the deny roles. I'd check all the permissions for the developers or that group for any denies and what roles they are members of.
Sue
December 13, 2017 at 2:15 pm
Thanks, but I've checked the DENY roles about 10 times now and there is no deny on anything set. I did make it a little further in troubleshooting though. After running through a few tests with a member of this development group, I found that they could indeed access the metadata from some of the other databases on this Instance, while being disallowed from seeing the data---exactly what I wanted. The key difference on the databases in which this is working correctly, is that someone along the way has added a lot of SYS schema securables with SELECT on them to the Public role on said database...and the securables are the same ones that are being complained about in the error messages.
So, I don;t guess you can set this up at the Instance level after all; although, I do wonder what View Definition at the Instance Level means if it doesnt carry itself down to all the databases. Seems weird I would have to explicitly add all the dependent SYS schema objects needed to see the Metadata, on each database.
December 13, 2017 at 2:46 pm
BTW, I got it to work by adding the Public Role within the problematic database, to the SYS schema with SELECT and View Definition all under the same database...and that had the desired effect. I was really hoping I could achieve the same thing at the Instance Level---in one place versus per database. If anyone has more feedback on getting this done at the Instance Level versus Database, I'm all ears.
Thanks,
Chris
December 13, 2017 at 2:47 pm
chris_barnhart - Wednesday, December 13, 2017 2:15 PMThanks, but I've checked the DENY roles about 10 times now and there is no deny on anything set. I did make it a little further in troubleshooting though. After running through a few tests with a member of this development group, I found that they could indeed access the metadata from some of the other databases on this Instance, while being disallowed from seeing the data---exactly what I wanted. The key difference on the databases in which this is working correctly, is that someone along the way has added a lot of SYS schema securables with SELECT on them to the Public role on said database...and the securables are the same ones that are being complained about in the error messages.So, I don;t guess you can set this up at the Instance level after all; although, I do wonder what View Definition at the Instance Level means if it doesnt carry itself down to all the databases. Seems weird I would have to explicitly add all the dependent SYS schema objects needed to see the Metadata, on each database.
View any definition at the server level isn't the same as view definition at the database level. There is meta data at the instance level and meta data at the database level.
I thought you did this but I now I don't think so from what you wrote - Did you grant view definition at the database level for the appropriate databases or just at the instance level?
I think it's a good design to have this scoped this way. I would rather not grant view definition at the server level and have it propagate down to every database.
Sue
December 13, 2017 at 2:54 pm
Sue_H - Wednesday, December 13, 2017 2:47 PMchris_barnhart - Wednesday, December 13, 2017 2:15 PMThanks, but I've checked the DENY roles about 10 times now and there is no deny on anything set. I did make it a little further in troubleshooting though. After running through a few tests with a member of this development group, I found that they could indeed access the metadata from some of the other databases on this Instance, while being disallowed from seeing the data---exactly what I wanted. The key difference on the databases in which this is working correctly, is that someone along the way has added a lot of SYS schema securables with SELECT on them to the Public role on said database...and the securables are the same ones that are being complained about in the error messages.So, I don;t guess you can set this up at the Instance level after all; although, I do wonder what View Definition at the Instance Level means if it doesnt carry itself down to all the databases. Seems weird I would have to explicitly add all the dependent SYS schema objects needed to see the Metadata, on each database.
View any definition at the server level isn't the same as view definition at the database level. There is meta data at the instance level and meta data at the database level.
I thought you did this but I now I don't think so from what you wrote - Did you grant view definition at the database level for the appropriate databases or just at the instance level?
I think it's a good design to have this scoped this way. I would rather not grant view definition at the server level and have it propagate down to every database.Sue
Yes, I was trying to perform at the Instance Level, but I've learned along the way that View Definitions at the Instance Level will not apply to the metadata at the database level...I was under the misunderstanding of a master hiearchy that pciked up more attributes as you drilled down further. Thanks for the feedback...I have now add View Definitions at the database level for the Public role and had the desired effect. I need to go back and test though, as I added Public as Select for the SYS schema, versus View Definition on the entire DB.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply