Skip to main content
13 events
when toggle format what by license comment
Dec 10, 2016 at 13:24 vote accept Zach Smith
Dec 9, 2016 at 12:52 comment added Dan Guzman @TaraKizer, have you considered cross-database ownership chaining? That would allow you to grant SELECT permissions on the view with no granted table permissions for view users. The tables will be editable only for users that have direct table permissions.
Dec 8, 2016 at 22:11 comment added SQLDevDBA @TaraKizer I already do it via db_denydatareader and db_denydatawriter. Whatever user I give those to can't see any objects at all in a Database. Once I grant them specific SELECT on an object, then they're able to see the object in the Object Browser and Select from it. Just wanted to see if there were any other methods of doing so. Thanks for all your contributions!
Dec 8, 2016 at 22:00 comment added Tara Kizer @MguerraTorres I'm not sure, but I don't think that's possible.
Dec 8, 2016 at 21:51 comment added SQLDevDBA Nice, thanks. What if I don't want them to even know the other objects exist (via the Object Explorer or Information_Schema). Is deny the only way?
Dec 8, 2016 at 21:49 comment added Tara Kizer @MguerraTorres, you could do that, but I just grant the needed permissions and then use deny as necessary.
Dec 8, 2016 at 20:53 comment added SQLDevDBA (OhEmGee It's the great @TaraKizer). Related question: Is my SOP of Creating a reporting or application user with 'db_denydatareader' and 'db_denydatawriter' and only giving them CRUD (SELECT/UPDATE/DELETE) as needed on specific objects correct? It's basically my way of trying to lock down as much as possible. Any thoughts?
Dec 8, 2016 at 20:18 comment added Tara Kizer You could try a trigger where it will rollback with transaction if it's initiated from a remote database. I don't have the code for that though.
Dec 8, 2016 at 19:56 comment added Zach Smith I want the tables in DB 1 to be editable only when accessed as that database (with a user who has permissions on that database). I want views to be select only for every user on DB 2 (I.e. no UPDATE, INSERT, DELETE, etc). However the view itself needs to be alterable by the same users that cannot update data through that view
Dec 8, 2016 at 19:52 comment added Tara Kizer @ZachSmith, but who needs those update permissions on the tables? And who needs the access denied?
Dec 8, 2016 at 19:50 history edited Tara Kizer CC BY-SA 3.0
added 197 characters in body
Dec 8, 2016 at 19:50 comment added Zach Smith The underlying tables (in a separate database) need to update permissions as though. Can i grant SELECT permission to just the view?
Dec 8, 2016 at 19:42 history answered Tara Kizer CC BY-SA 3.0