January 5, 2016 at 10:33 am
I've got a Database with multiple schemas and need to provide another team access to data from this Database
So I've created a Schema called RPT
I've then create a user Reports
CREATE USER Reports FOR LOGIN report_user WITH DEFAULT_SCHEMA=[RPT]
GO
CREATE ROLE Reporting AUTHORIZATION report_user (Not sure if this is correct user)
GO
exec sp_addrolemember @rolename = 'Reporting', @membername = 'Reports'
GO
GRANT CREATE VIEW TO Reporting
GRANT CREATE TABLE TO Reporting
grant select , VIEW DEFINITION ON [RPT].[MyView] to Reporting
When I log into SSMS as report_user
I see my Database
Under Tables, I see nothing
Under Views I see
[RPT].[MyView]
I right click and Select top 1000 Rows
Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'TheData', database 'MyDatabase', schema 'MainData'.
I've googled this like crazy and based on my findings what I've done should work
But it's not working
If I
GRANT SELECT, VIEW DEFINITION ON [MainData].[TheData] TO Reports;
I then get
Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'Table2', database 'MyDatabase', schema 'MainData'.
If I Grant on all tables my View works
But then the user can see these tables, which is what I don't want
January 5, 2016 at 4:07 pm
First, be careful with the UI options in Management Studio. Some of the dialogs that pretend to select from a view actually read the underlying definition and then connect straight to the tables - at least it was that way in older versions of SSMS (I stopped checking at one point),
The safest check is to open a query window and type a query - in your case, SELECT TOP(10) Name, Those, Columns FROM RPT.MyView.
If that does not work, then check the ownership of the view and of the underlying tables. If they are all owned by the same user and they are all in the same database, then ownership chainiing applies - meaning that the owner of a table can denny other access to the table, but grant access through a view. Technically speaking, this means that if you have access to the view, then permissions on objects with the same owner and in the same database are not checked.
But the same does not apply when different owners are involved. (Would be weird - I grant you access to read my table but deny Steve that access, you create a view on my table and grant access to Steve and now Steve can bypass my stop sign). So when the underlying table is not owned by the same user that owns the view, then SQL Server will not only verify that the reporting user has access to the view, but it will also verify that the table owner allows the reporting user to see their table.
(In most database the best way to deal with ownership is to have all objects owned by the same user, so that ownership chaining always applies).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply