October 1, 2018 at 10:34 am
This is a head scratcher.
I have a windows user, I even deleted and recreated the users account on the server and database, they are unable to SELECT from any tables in one specific database.
I verified that there are no DENY permissions set that could perhaps be blocking the user
select object_name(major_id) as object,
user_name(grantee_principal_id) as grantee,
user_name(grantor_principal_id) as grantor,
permission_name,
state_desc, class
from sys.database_permissions
I am really not sure what else to check here, I even made the user part of the DB_OWNER group, but it didn't make a difference.
This database uses a different schema than DBO, so I added the user to the custom SCHEMA ... with SELECT, still nothing...
October 1, 2018 at 10:50 am
Maxer - Monday, October 1, 2018 10:34 AMThis is a head scratcher.I have a windows user, I even deleted and recreated the users account on the server and database, they are unable to SELECT from any tables in one specific database.
I verified that there are no DENY permissions set that could perhaps be blocking the user
select object_name(major_id) as object,
user_name(grantee_principal_id) as grantee,
user_name(grantor_principal_id) as grantor,
permission_name,
state_desc, class
from sys.database_permissionsI am really not sure what else to check here, I even made the user part of the DB_OWNER group, but it didn't make a difference.
This database uses a different schema than DBO, so I added the user to the custom SCHEMA ... with SELECT, still nothing...
You can use sys.fn_my_permissions, execute as the user, and verify the effective permissions. You would also want to check the role membership for the user.
Sue
October 1, 2018 at 11:03 am
Maxer - Monday, October 1, 2018 10:34 AMThis is a head scratcher.I have a windows user, I even deleted and recreated the users account on the server and database, they are unable to SELECT from any tables in one specific database.
I verified that there are no DENY permissions set that could perhaps be blocking the user
select object_name(major_id) as object,
user_name(grantee_principal_id) as grantee,
user_name(grantor_principal_id) as grantor,
permission_name,
state_desc, class
from sys.database_permissionsI am really not sure what else to check here, I even made the user part of the DB_OWNER group, but it didn't make a difference.
This database uses a different schema than DBO, so I added the user to the custom SCHEMA ... with SELECT, still nothing...
Have you granted them SELECT permissions to each table?
October 1, 2018 at 11:48 am
Jonathan AC Roberts - Monday, October 1, 2018 11:03 AMMaxer - Monday, October 1, 2018 10:34 AMThis is a head scratcher.I have a windows user, I even deleted and recreated the users account on the server and database, they are unable to SELECT from any tables in one specific database.
I verified that there are no DENY permissions set that could perhaps be blocking the user
select object_name(major_id) as object,
user_name(grantee_principal_id) as grantee,
user_name(grantor_principal_id) as grantor,
permission_name,
state_desc, class
from sys.database_permissionsI am really not sure what else to check here, I even made the user part of the DB_OWNER group, but it didn't make a difference.
This database uses a different schema than DBO, so I added the user to the custom SCHEMA ... with SELECT, still nothing...
Have you granted them SELECT permissions to each table?
Yes, I granted explicit select permission to a few of the tables to test, and tried querying from said tables. Same result.
I also checked the user's roles, they are only in: db_datareader
October 1, 2018 at 12:54 pm
When you test - are you testing with objects that are schema qualified?
SELECT ... FROM myschema.table WHERE ...
Or are you testing by just referencing the table?
SELECT ... FROM table WHERE ...
If the latter - SQL Server probably will not find the table and you would get the specified error. Also - insure the user has the correct default schema defined in that database and not dbo or some other schema (like the user's personal schema).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 1, 2018 at 2:13 pm
Jeffrey Williams 3188 - Monday, October 1, 2018 12:54 PMWhen you test - are you testing with objects that are schema qualified?SELECT ... FROM myschema.table WHERE ...
Or are you testing by just referencing the table?
SELECT ... FROM table WHERE ...
If the latter - SQL Server probably will not find the table and you would get the specified error. Also - insure the user has the correct default schema defined in that database and not dbo or some other schema (like the user's personal schema).
Good point thanks!
However, the table is being addressed.
I also went and added the user to that SCHEMA with SELECT granted again just to validate they were in there, still no good.
October 1, 2018 at 2:25 pm
Maxer - Monday, October 1, 2018 2:13 PMJeffrey Williams 3188 - Monday, October 1, 2018 12:54 PMWhen you test - are you testing with objects that are schema qualified?SELECT ... FROM myschema.table WHERE ...
Or are you testing by just referencing the table?
SELECT ... FROM table WHERE ...
If the latter - SQL Server probably will not find the table and you would get the specified error. Also - insure the user has the correct default schema defined in that database and not dbo or some other schema (like the user's personal schema).
Good point thanks!
However, the table is being addressed.
I also went and added the user to that SCHEMA with SELECT granted again just to validate they were in there, still no good.
What is the default schema assigned to the user? Is it the same schema that you are granting the user access to? Who owns that schema - is it dbo or some other user?
It sounds like there is a break in the ownership chain somewhere that is preventing that user from accessing that schema.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 1, 2018 at 2:40 pm
Interesting on the ownership chaining.
The user's default schema is dbo but this table is in the schema called 'DB'
I altered the user to be DB for the default schema. Still the same results:
The SELECT permission was denied on the object 'Table_Name', database 'DB_Database', schema 'DB'.
The schema DB is owned by DB.
CREATE USER [domain\user] FOR LOGIN [domain\user] WITH DEFAULT_SCHEMA=[DB]
So strange....
October 1, 2018 at 4:28 pm
Maxer - Monday, October 1, 2018 2:40 PMInteresting on the ownership chaining.The user's default schema is dbo but this table is in the schema called 'DB'
I altered the user to be DB for the default schema. Still the same results:
The SELECT permission was denied on the object 'Table_Name', database 'DB_Database', schema 'DB'.The schema DB is owned by DB.
CREATE USER [domain\user] FOR LOGIN [domain\user] WITH DEFAULT_SCHEMA=[DB]
So strange....
Try changing the owner of the dB schema to dbo
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 1, 2018 at 6:57 pm
Who owns the non-dbo schema?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 2, 2018 at 8:01 am
That schema is owned by the user all called "DB".
What are the impacts if I change the schema DB owner to be dbo as opposed to its current owner the user called DB?
Will that cause any downstream issues? Break any existing privs etc?
October 2, 2018 at 8:16 am
Jeffrey Williams 3188 - Monday, October 1, 2018 4:28 PMMaxer - Monday, October 1, 2018 2:40 PMInteresting on the ownership chaining.The user's default schema is dbo but this table is in the schema called 'DB'
I altered the user to be DB for the default schema. Still the same results:
The SELECT permission was denied on the object 'Table_Name', database 'DB_Database', schema 'DB'.The schema DB is owned by DB.
CREATE USER [domain\user] FOR LOGIN [domain\user] WITH DEFAULT_SCHEMA=[DB]
So strange....
Try changing the owner of the dB schema to dbo
OK, I made that change, still no luck....
October 2, 2018 at 8:22 am
OK this is more strange:
We took a NEW user, should be the same as the current one...he can query everything just fine... without any issues.
Is there a way I can compare two windows accounts to see why one is different than the other with privs?
October 2, 2018 at 8:27 am
Maxer - Tuesday, October 2, 2018 8:22 AMOK this is more strange:We took a NEW user, should be the same as the current one...he can query everything just fine... without any issues.
Is there a way I can compare two windows accounts to see why one is different than the other with privs?
Was the original user part of an AD group where the AD group was denied privs?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 2, 2018 at 9:09 am
Interesting idea about the deny group.
I checked there is ONE Windows AD group that was set to db_denydatareader.
and THAT WAS IT!~!!! THANKS!!!!!
I ran this for both users:
select
s.name as 'Schema'
,o.name as Object
,dp.name as user_group_name
,p.class_desc as class
,p.permission_name as permission
,p.state_desc as state
,case major_id
when 0 then state_desc + ' ' + permission_name + ' to [' + dp.name + ']' COLLATE LATIN1_General_CI_AS
else state_desc + ' ' + permission_name + ' on ['+ s.name + '].[' + o.name + '] to [' + dp.name + ']' COLLATE LATIN1_General_CI_AS
end as [how granted]
from
sys.database_permissions as p
left join sys.all_objects as o on p.major_id = o.object_id
left join sys.schemas as s on o.schema_id = s.schema_id
left join sys.database_principals as dp on p.grantee_principal_id = dp.principal_id
where p.major_id >= 0 -- Exclude system objects
No material differences: (I only included the middle 3 columns below)
The working user:
class permission state
DATABASE CONNECT GRANT
DATABASE SELECT GRANT_WITH_GRANT_OPTION
The user getting the SELECT error:
class permission state
DATABASE CONNECT GRANT_WITH_GRANT_OPTION
DATABASE SELECT GRANT_WITH_GRANT_OPTION
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply