January 11, 2017 at 11:28 pm
Create a view involving multiple tables from mulitple schemas and gave below permission
use [TEST_PROD]
GO
GRANT SELECT ON [dbo].[View_1] TO [esan]
GO
getting error that permission was denied on base table. How can achieve this?
January 12, 2017 at 3:26 am
Rechana Rajan - Wednesday, January 11, 2017 11:28 PMCreate a view involving multiple tables from mulitple schemas and gave below permissionuse [TEST_PROD]GOGRANT SELECT ON [dbo].[View_1] TO [esan]GOgetting error that permission was denied on base table. How can achieve this?
Please post the view definition, the statement you're running when you get the error, and the exact error message.
Thanks
John
January 12, 2017 at 3:48 am
John Mitchell-245523 - Thursday, January 12, 2017 3:26 AMRechana Rajan - Wednesday, January 11, 2017 11:28 PMCreate a view involving multiple tables from mulitple schemas and gave below permissionuse [TEST_PROD]GOGRANT SELECT ON [dbo].[View_1] TO [esan]GOgetting error that permission was denied on base table. How can achieve this?Please post the view definition, the statement you're running when you get the error, and the exact error message.
Thanks
John
Thanks John,
This is what i used.
CREATE VIEW [dbo].[View_1]
AS
SELECT RRN.AS.oid AS Expr1, RRN.BTType.oid, RRN.CSHistory.sId, dbo.CB.MainProfileID, RRN.Void.VoidID
FROM RRN.AS INNER JOIN
RRN.BTType ON RRN.ASettings.oid = RRN.BTType.oid CROSS JOIN
RRN.CSHistory CROSS JOIN
dbo.CB CROSS JOIN
RRN.Void
grant select on dbo.view_1 to Test
January 12, 2017 at 4:03 am
Rechana Rajan - Thursday, January 12, 2017 3:48 AMJohn Mitchell-245523 - Thursday, January 12, 2017 3:26 AMRechana Rajan - Wednesday, January 11, 2017 11:28 PMCreate a view involving multiple tables from mulitple schemas and gave below permissionuse [TEST_PROD]GOGRANT SELECT ON [dbo].[View_1] TO [esan]GOgetting error that permission was denied on base table. How can achieve this?Please post the view definition, the statement you're running when you get the error, and the exact error message.
Thanks
JohnThanks John,
This is what i used.CREATE VIEW [dbo].[View_1]
AS
SELECT RRN.AS.oid AS Expr1, RRN.BTType.oid, RRN.CSHistory.sId, dbo.CB.MainProfileID, RRN.Void.VoidID
FROM RRN.AS INNER JOIN
RRN.BTType ON RRN.ASettings.oid = RRN.BTType.oid CROSS JOIN
RRN.CSHistory CROSS JOIN
dbo.CB CROSS JOIN
RRN.Voidgrant select on dbo.view_1 to Test
One out of three's a start. Please now post the statement you're running when you get the error, and the exact error message. Who have you grant SELECT to - Test or esan? Who are you connected as when you get the error? By the way, your query doesn't make sense - you mention a table called ASettings in your join predicate, but that table isn't mentioned anywhere else.
John
January 12, 2017 at 4:59 am
the ownership chain is broken as you reference object from a different schema.
Answers to the questions above are required
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 12, 2017 at 5:25 am
Thanks John and Perry,
Sorry for the confusion.
CREATE VIEW [dbo].[View_1]
AS
SELECT RRN.ASettings.oid AS Expr1, RRN.BTType.oid, RRN.CSHistory.sId, dbo.CB.MainProfileID, RRN.Void.VoidID
FROM RRN.ASettings INNER JOIN
RRN.BTType ON RRN.ASettings.oid = RRN.BTType.oid CROSS JOIN
RRN.CSHistory CROSS JOIN
dbo.CB CROSS JOIN
RRN.Void
grant select on dbo.view_1 to Test
select * from [dbo].[View_1]
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'Void', database 'TEST', schema 'RRN'.
The select was run from login Test which have only the above permisison.
January 12, 2017 at 5:52 am
I think Perry's right - it's to do with ownership chaining. What results do you get from these two queries?
SELECT
name
,USER_NAME(principal_id) AS SchemaOwner
FROM sys.schemas
SELECT
name
,COALESCE(USER_NAME(principal_id),'<Schema owner>') AS ObjectOwner
,type_desc
FROM sys.objects
WHERE (name = 'ASettings' AND SCHEMA_NAME(schema_id) = 'RRN')
OR (name = 'BTType' AND SCHEMA_NAME(schema_id) = 'RRN')
OR (name = 'CSHistory' AND SCHEMA_NAME(schema_id) = 'RRN')
OR (name = 'CB' AND SCHEMA_NAME(schema_id) = 'dbo')
OR (name = 'Void' AND SCHEMA_NAME(schema_id) = 'RRN')
OR (name = 'View_1' AND SCHEMA_NAME(schema_id) = 'dbo')
John
Edit - sorted IF codes.
January 12, 2017 at 6:06 am
Rechana Rajan - Thursday, January 12, 2017 5:25 AMThanks John and Perry,Sorry for the confusion.
CREATE VIEW [dbo].[View_1]
AS
SELECT RRN.ASettings.oid AS Expr1, RRN.BTType.oid, RRN.CSHistory.sId, dbo.CB.MainProfileID, RRN.Void.VoidID
FROM RRN.ASettings INNER JOIN
RRN.BTType ON RRN.ASettings.oid = RRN.BTType.oid CROSS JOIN
RRN.CSHistory CROSS JOIN
dbo.CB CROSS JOIN
RRN.Voidgrant select on dbo.view_1 to Test
select * from [dbo].[View_1]
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'Void', database 'TEST', schema 'RRN'.The select was run from login Test which have only the above permisison.
The grant of permissions on the view in the dbo schema will only work when all referenced objects are within the same schema.
Since some objects are in another schema, dbo no longer owns the right to access these objects so permissions to the underlying objects would be necessary.
Please explain a little more about what you are trying to achieve and what you have done so far
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 13, 2017 at 1:12 pm
If your RRN schema was instead DBO, then the schema "dbo" would be the owner of all the objects involved. As it stands the DBO owner cannot access those what are owned by RRN. Even though you have access to the view (owned by "dbo") you and "dbo" dont have access to the objects owned by RRN. I know it sounds a little confusing. As to what to do about it... I would suggest you read to learn about ownership chains and their purpose. That so you don't implement some kind of permissions hacking that defeats the purpose.
They way I have seen schemas used is more to do with the source of the data that came to the warehouse rather than who owns it (like different developers on the same development instance). So maybe those objects can be transferred to DBO?
----------------------------------------------------
January 15, 2017 at 12:13 am
Perry Whittle - Thursday, January 12, 2017 6:06 AMRechana Rajan - Thursday, January 12, 2017 5:25 AMThanks John and Perry,Sorry for the confusion.
CREATE VIEW [dbo].[View_1]
AS
SELECT RRN.ASettings.oid AS Expr1, RRN.BTType.oid, RRN.CSHistory.sId, dbo.CB.MainProfileID, RRN.Void.VoidID
FROM RRN.ASettings INNER JOIN
RRN.BTType ON RRN.ASettings.oid = RRN.BTType.oid CROSS JOIN
RRN.CSHistory CROSS JOIN
dbo.CB CROSS JOIN
RRN.Voidgrant select on dbo.view_1 to Test
select * from [dbo].[View_1]
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'Void', database 'TEST', schema 'RRN'.The select was run from login Test which have only the above permisison.
The grant of permissions on the view in the dbo schema will only work when all referenced objects are within the same schema.
Since some objects are in another schema, dbo no longer owns the right to access these objects so permissions to the underlying objects would be necessary.Please explain a little more about what you are trying to achieve and what you have done so far
Thanks for the reply.
We have a database with multiple schema and I am trying to create a view for reporting purpose joining multiple schema. Will create a separate user to have access only to that view.
January 15, 2017 at 9:46 pm
This was removed by the editor as SPAM
January 16, 2017 at 2:31 am
Rechana Rajan - Sunday, January 15, 2017 12:13 AMThanks for the reply.We have a database with multiple schema and I am trying to create a view for reporting purpose joining multiple schema. Will create a separate user to have access only to that view.
Yikes! That's one more password to manage, and one more door for a potential attacker to come in through. You're treating the symptom, not the cause. What are you going to do when you create another view where the permissions don't work - create another new user, or give more permissions to the existing one? Eventually you'll have either so many users you can't manager them, or a user that has access to (nearly) all of the underlying tables. Please give the results of the query I posted earlier, and let's see whether we can fix the problem at source.
John
January 16, 2017 at 5:37 am
John Mitchell-245523 - Monday, January 16, 2017 2:31 AMRechana Rajan - Sunday, January 15, 2017 12:13 AMThanks for the reply.We have a database with multiple schema and I am trying to create a view for reporting purpose joining multiple schema. Will create a separate user to have access only to that view.
Yikes! That's one more password to manage, and one more door for a potential attacker to come in through. You're treating the symptom, not the cause. What are you going to do when you create another view where the permissions don't work - create another new user, or give more permissions to the existing one? Eventually you'll have either so many users you can't manager them, or a user that has access to (nearly) all of the underlying tables. Please give the results of the query I posted earlier, and let's see whether we can fix the problem at source.
John
Thanks John,
Please find the result below.
name SchemaOwner
RRN dbo
name ObjectOwner type_desc
View_1 <Schema owner> VIEW
BTType <Schema owner> USER_TABLE
CB <Schema owner> USER_TABLE
CSHistory <Schema owner> USER_TABLE
ASettings <Schema owner> USER_TABLE
Void <Schema owner> USER_TABLE
January 16, 2017 at 5:59 am
Rechana Rajan - Monday, January 16, 2017 5:37 AMJohn Mitchell-245523 - Monday, January 16, 2017 2:31 AMRechana Rajan - Sunday, January 15, 2017 12:13 AMThanks for the reply.We have a database with multiple schema and I am trying to create a view for reporting purpose joining multiple schema. Will create a separate user to have access only to that view.
Yikes! That's one more password to manage, and one more door for a potential attacker to come in through. You're treating the symptom, not the cause. What are you going to do when you create another view where the permissions don't work - create another new user, or give more permissions to the existing one? Eventually you'll have either so many users you can't manager them, or a user that has access to (nearly) all of the underlying tables. Please give the results of the query I posted earlier, and let's see whether we can fix the problem at source.
John
Thanks John,
Please find the result below.
name SchemaOwner
pcs dboname ObjectOwner type_desc
View_1 <Schema owner> VIEW
BTType <Schema owner> USER_TABLE
CB <Schema owner> USER_TABLE
CSHistory <Schema owner> USER_TABLE
ASettings <Schema owner> USER_TABLE
Void <Schema owner> USER_TABLE
How come you now have a schema called pcs but not one called RRN?
John
January 18, 2017 at 3:04 am
Sorry my bad its RRN
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply