May 14, 2015 at 1:29 pm
I'm really stuck on details of how to give a user called SHOP_REPORTING access to a view without giving them access to the base table. I think I'm close but I'm still getting the error:
The SELECT permission was denied on the object 'system_keys', database 'store', schema 'dbo'.
Can anyone see what I'm doing wrong in the code below? Any help would be GREATLY appreciated! 🙂
CREATE USER SHOP_REPORTING FOR LOGIN SHOP_REPORTING
GO
CREATE SCHEMA SHOPReports AUTHORIZATION SHOP_REPORTING
GO
ALTER USER SHOP_REPORTING WITH DEFAULT_SCHEMA = SHOPReports
go
CREATE VIEW SHOPReports.SHOP_Images_View WITH schemabinding AS
SELECT[system_keys_guid]
,[system_key]
,[system_value]
,[f_active]
,[system_agency]
,[system_module]
,[comment]
,[eis_value]
,[SHOP_value]
FROMdbo.system_keys
WHERE system_key = 'IMAGEPATH'
GO
CREATE ROLE Reporting AUTHORIZATION SHOP_REPORTING
EXEC sp_addrolemember @rolename = 'Reporting', @membername = 'LCDOM100\LCISPRE'
GO
GRANT SELECT , VIEW DEFINITION ON SHOPReports.SHOP_Images_View TO Reporting
GO
--Testing view
EXECUTE AS USER = 'SHOP_REPORTING'
SELECT * FROM SHOPReports.SHOP_Images_View
go
SELECT * FROM dbo.system_keys
GO
revert
May 14, 2015 at 1:35 pm
i think if you don't use the schema, and leave everything in dbo, you'll be fine.
as soon as you go cross schema, the user needs access to the underlying tables.
leaving the view in the dbo schema would allow the user to see it the view, but not the underlying table.
CREATE VIEW dbo.SHOP_Images_View WITH schemabinding AS
SELECT[system_keys_guid]
,[system_key]
,[system_value]
,[f_active]
,[system_agency]
,[system_module]
,[comment]
,[eis_value]
,[SHOP_value]
FROMdbo.system_keys
WHERE system_key = 'IMAGEPATH'
GO
CREATE ROLE Reporting AUTHORIZATION SHOP_REPORTING
EXEC sp_addrolemember @rolename = 'Reporting', @membername = 'LCDOM100\LCISPRE'
GO
GRANT SELECT , VIEW DEFINITION ON dbo.SHOP_Images_View TO Reporting
GO
--Testing view
EXECUTE AS USER = 'SHOP_REPORTING'
SELECT * FROM dbo.SHOP_Images_View
go
SELECT * FROM dbo.system_keys
GO
revert
Lowell
May 14, 2015 at 1:37 pm
How many times do you get the error message? You should get it once, since you run a query against the base table as well.
If you get it twice, there is something which is not the way we expect it to be. That is, your setup should work, as long as the view and the table has the same owner.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
May 14, 2015 at 2:42 pm
I dropped the original view and created the same view in the dbo schema. Now I get 2 SELECT permission was denied error messages.
May 14, 2015 at 2:52 pm
I did not look closely enough at the original post. The fact that the view is in a different schema than the table does not matter as such. But the schemas have different owners. Again, this as such does not matter, but by default objects created in a schema are owned by the schema owner. And once there are different owners, ownership chaining no longer applies.
If you create the view in the dbo schema, you need to grant SHOP_REPORTING user SELECT permission on the view. The same applies, if you let dbo to be the owner of the SHOP_REPORTING schema.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
May 14, 2015 at 3:00 pm
Thanks, so much, Erland! That did it! What I don't understand is why granting select to the Reporting role didn't give the user select permission.
This is what I added in order for that user to select the view but not the underlying table:
GRANT SELECT , VIEW DEFINITION ON [dbo].[SHOP_Images_View] TO SHOP_REPORTING
GO
May 14, 2015 at 3:08 pm
In the script you posted, you never added SHOP_REPORTING to the Reporting role, only a Windows user...
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
May 14, 2015 at 4:10 pm
Erland,
Crikey, you're right. I made too many changes and missed that one.
Thanks so much for your help! You made my day.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply