January 18, 2017 at 3:13 am
John
January 19, 2017 at 5:45 am
John Mitchell-245523 - Wednesday, January 18, 2017 3:13 AMGood. Please post the full results of that query.John
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 19, 2017 at 5:51 am
John
January 22, 2017 at 2:44 am
Sorry.Already mentioned in thread.
January 22, 2017 at 3:29 am
John Mitchell-245523 - Thursday, January 19, 2017 5:51 AMThat can't be the full results. In your query, you are referencing one or more tables in the dbo schema, and yet the dbo schema does not appear in the first result set.John
name SchemaOwner
dbo dbo
guest guest
INFORMATION_SCHEMA INFORMATION_SCHEMA
sys sys
RRN dbo
RRNNIC dbo
db_owner dbo
db_accessadmin db_accessadmin
db_securityadmin db_securityadmin
db_ddladmin db_ddladmin
db_backupoperator db_backupoperator
db_datareader db_datareader
db_datawriter db_datawriter
db_denydatareader db_denydatareader
db_denydatawriter db_denydatawriter
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 23, 2017 at 3:03 am
create schema RRN authorization dbocreate table RRN.Asettings (oid int)
create table RRN.BTType (oid int)
create table RRN.CSHistory (sId int)
create table dbo.CB (MainProfileID int)
create table RRN.Void (VoidID int)
GO
create login Test with password = 'xxxxxxxxx'
create user Test from login Test
GO
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
GO
grant select on dbo.view_1 to Test
execute as user = 'Test'
select * from dbo.View_1
revert
The SELECT statement returned a result set (empty of course, but a result set nevertheless) and there was no error. The only thing I can think of is that you have a case-sensitive database, because your view is called View_1 but you granted permission on view_1. What do you get if you run this?SELECT DATABASEPROPERTYEX('TEST_DB','Collation')
John
January 23, 2017 at 5:59 am
John Mitchell-245523 - Monday, January 23, 2017 3:03 AMSince all schemas in question are owned by the user dbo, you shouldn't have a problem with ownership chaining. I recreated what I think your database looks like with the following code:create schema RRN authorization dbocreate table RRN.Asettings (oid int)
create table RRN.BTType (oid int)
create table RRN.CSHistory (sId int)
create table dbo.CB (MainProfileID int)
create table RRN.Void (VoidID int)
GOcreate login Test with password = 'xxxxxxxxx'
create user Test from login Test
GOCREATE 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
GOgrant select on dbo.view_1 to Test
execute as user = 'Test'
select * from dbo.View_1
revertThe SELECT statement returned a result set (empty of course, but a result set nevertheless) and there was no error. The only thing I can think of is that you have a case-sensitive database, because your view is called View_1 but you granted permission on view_1. What do you get if you run this?
SELECT DATABASEPROPERTYEX('TEST_DB','Collation')John
Thanks a lot for the support.
Query gave SQL_Latin1_General_CP1_CI_AS
When i tried to give Grant select on dbo.view_1 to Test got below message.
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
What i observed now is very strange that when i connect using the Login Test and access the view from SSMS i can see the result from from SSMS i am getting error.
use Test
select * from dbo.View_1
Msg 916, Level 14, State 1, Line 3
The server principal "TEST" is not able to access the database "Test" under the current security context.
January 23, 2017 at 7:30 am
Rechana Rajan - Monday, January 23, 2017 5:59 AMJohn Mitchell-245523 - Monday, January 23, 2017 3:03 AMSince all schemas in question are owned by the user dbo, you shouldn't have a problem with ownership chaining. I recreated what I think your database looks like with the following code:create schema RRN authorization dbocreate table RRN.Asettings (oid int)
create table RRN.BTType (oid int)
create table RRN.CSHistory (sId int)
create table dbo.CB (MainProfileID int)
create table RRN.Void (VoidID int)
GOcreate login Test with password = 'xxxxxxxxx'
create user Test from login Test
GOCREATE 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
GOgrant select on dbo.view_1 to Test
execute as user = 'Test'
select * from dbo.View_1
revertThe SELECT statement returned a result set (empty of course, but a result set nevertheless) and there was no error. The only thing I can think of is that you have a case-sensitive database, because your view is called View_1 but you granted permission on view_1. What do you get if you run this?
SELECT DATABASEPROPERTYEX('TEST_DB','Collation')John
Thanks a lot for the support.
Query gave SQL_Latin1_General_CP1_CI_AS
When i tried to give Grant select on dbo.view_1 to Test got below message.
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
What i observed now is very strange that when i connect using the Login Test and access the view from SSMS i can see the result from from SSMS i am getting error.use Test
select * from dbo.View_1Msg 916, Level 14, State 1, Line 3
The server principal "TEST" is not able to access the database "Test" under the current security context.
I'm quite confused now. I thought you'd already granted Test SELECT on the view? I'm also not clear on what the names of the database and the user are. But it looks as if Test is the owner of View_1, even though the query results you posted earlier show that View_1 is owned by the schema owner. Please will you post the results of this query:SELECT
class_desc
, USER_NAME(grantee_principal_id) AS Grantee
, permission_name
, state_desc
, OBJECT_NAME(major_id) ObjectName
, OBJECT_SCHEMA_NAME(major_id) InSchema
, minor_id
FROM sys.database_permissions
WHERE major_id >= 0
AND (OBJECT_NAME(major_id) IN ('Asettings','BTType','CSHistory','CB','Void')
OR USER_NAME(grantee_principal_id) = 'Test')
John
January 24, 2017 at 4:28 am
John Mitchell-245523 - Monday, January 23, 2017 7:30 AMRechana Rajan - Monday, January 23, 2017 5:59 AMJohn Mitchell-245523 - Monday, January 23, 2017 3:03 AMSince all schemas in question are owned by the user dbo, you shouldn't have a problem with ownership chaining. I recreated what I think your database looks like with the following code:create schema RRN authorization dbocreate table RRN.Asettings (oid int)
create table RRN.BTType (oid int)
create table RRN.CSHistory (sId int)
create table dbo.CB (MainProfileID int)
create table RRN.Void (VoidID int)
GOcreate login Test with password = 'xxxxxxxxx'
create user Test from login Test
GOCREATE 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
GOgrant select on dbo.view_1 to Test
execute as user = 'Test'
select * from dbo.View_1
revertThe SELECT statement returned a result set (empty of course, but a result set nevertheless) and there was no error. The only thing I can think of is that you have a case-sensitive database, because your view is called View_1 but you granted permission on view_1. What do you get if you run this?
SELECT DATABASEPROPERTYEX('TEST_DB','Collation')John
Thanks a lot for the support.
Query gave SQL_Latin1_General_CP1_CI_AS
When i tried to give Grant select on dbo.view_1 to Test got below message.
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
What i observed now is very strange that when i connect using the Login Test and access the view from SSMS i can see the result from from SSMS i am getting error.use Test
select * from dbo.View_1Msg 916, Level 14, State 1, Line 3
The server principal "TEST" is not able to access the database "Test" under the current security context.I'm quite confused now. I thought you'd already granted Test SELECT on the view? I'm also not clear on what the names of the database and the user are. But it looks as if Test is the owner of View_1, even though the query results you posted earlier show that View_1 is owned by the schema owner. Please will you post the results of this query:
SELECT
class_desc
, USER_NAME(grantee_principal_id) AS Grantee
, permission_name
, state_desc
, OBJECT_NAME(major_id) ObjectName
, OBJECT_SCHEMA_NAME(major_id) InSchema
, minor_id
FROM sys.database_permissions
WHERE major_id >= 0
AND (OBJECT_NAME(major_id) IN ('Asettings','BTType','CSHistory','CB','Void')
OR USER_NAME(grantee_principal_id) = 'Test')John
Thanks Again John.
Please find the result below
class_desc Grantee permission_name state_desc ObjectName InSchema minor_id
DATABASE TEST CONNECT GRANT NULL NULL 0
OBJECT_OR_COLUMN TEST SELECT GRANT View_1 dbo 0
January 24, 2017 at 4:42 am
Rechana Rajan - Tuesday, January 24, 2017 4:28 AMThanks Again John.Please find the result below
class_desc Grantee permission_name state_desc ObjectName InSchema minor_id
DATABASE TEST CONNECT GRANT NULL NULL 0
OBJECT_OR_COLUMN TEST SELECT GRANT View_1 dbo 0
I don't know what's going on, then. The only thing I can think is that there's something you haven't told us. You might try dropping the view and recreating it using the statements you've posted in this thread. Better still, script out the tables, the view and the user and recreate them in a new database. Do you still get the error? If you don't, work back and see what's different between the two databases. If you do, please post the scripts and I'll see whether I can reproduce your problem.
John
January 24, 2017 at 11:53 pm
John Mitchell-245523 - Tuesday, January 24, 2017 4:42 AMRechana Rajan - Tuesday, January 24, 2017 4:28 AMThanks Again John.Please find the result below
class_desc Grantee permission_name state_desc ObjectName InSchema minor_id
DATABASE TEST CONNECT GRANT NULL NULL 0
OBJECT_OR_COLUMN TEST SELECT GRANT View_1 dbo 0I don't know what's going on, then. The only thing I can think is that there's something you haven't told us. You might try dropping the view and recreating it using the statements you've posted in this thread. Better still, script out the tables, the view and the user and recreate them in a new database. Do you still get the error? If you don't, work back and see what's different between the two databases. If you do, please post the scripts and I'll see whether I can reproduce your problem.
John
Thanks a lot for the support John.
I created everything on another DB and its working fine...Thanks again.
So the permission chain will break if the owner of the objects is different and its regardless of schema name.
January 25, 2017 at 2:32 am
Rechana Rajan - Tuesday, January 24, 2017 11:53 PMSo the permission chain will break if the owner of the objects is different and its regardless of schema name.
What's important is the schema owner, not the schema name. Objects don't have owners as they did in pre-2005 days. In the old days, if an object was owned by dbo, it was called dbo.objectname. If it was owned by a user called RRN, it was called RRN.objectname. Nowadays, schemas (which are nothing more than namespaces) are independent of users, and you can have a schema called RRN without having a user of the same name; that schema can be owned by any database user. Since the tables are all in schemas owned by dbo (in your new database, at least), and the view is also in such a schema, ownership chaining works. If you were to change the owner of the RRN schema to a different user, you'd be likely to start getting access errors.
John
January 25, 2017 at 4:41 am
John Mitchell-245523 - Wednesday, January 25, 2017 2:32 AMRechana Rajan - Tuesday, January 24, 2017 11:53 PMSo the permission chain will break if the owner of the objects is different and its regardless of schema name.What's important is the schema owner, not the schema name. Objects don't have owners as they did in pre-2005 days. In the old days, if an object was owned by dbo, it was called dbo.objectname. If it was owned by a user called RRN, it was called RRN.objectname. Nowadays, schemas (which are nothing more than namespaces) are independent of users, and you can have a schema called RRN without having a user of the same name; that schema can be owned by any database user. Since the tables are all in schemas owned by dbo (in your new database, at least), and the view is also in such a schema, ownership chaining works. If you were to change the owner of the RRN schema to a different user, you'd be likely to start getting access errors.
John
Thanks a ton John..🙂
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply