SELECT permission denied
-
AFTER a DROP TABLE / CREATE TABLE command, some users who previously could SELECT from the table are now being denied.
AFAIK, there have been no permissions set at the table level, only at the database level.
User who is denied can SELECT from other tables.
Is there a way to set table to inherit permissions from the database?
-
If they're getting denied access to just one table, then there's a object-level permission set on that table. Check the DMVs for the details, you can then use REVOKE to remove that permission.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
inevercheckthis2002 (8/23/2016)
AFTER a DROP TABLE / CREATE TABLE command, some users who previously could SELECT from the table are now being denied.AFAIK, there have been no permissions set at the table level, only at the database level.
User who is denied can SELECT from other tables.
Is there a way to set table to inherit permissions from the database?
In addition to checking object permissions, you want to check what scope of permissions the users have. It could be they have schema level permissions and the object that was dropped and recreated is actually now in a different schema.
Sue
-
GilaMonster (8/23/2016)
If they're getting denied access to just one table, then there's a object-level permission set on that table. Check the DMVs for the details, you can then use REVOKE to remove that permission.EXEC sp_table_privileges
@table_name='MY_TABLE';
Returns zero rows
-
EXEC sp_table_privileges
@table_name='MY_TABLE';
Returns zero rows
Several of the columns always return a value. Do you have select permissions for the schema MY_TABLE belongs to? That's the only thing I can think of that would result in no rows.
Sue
-
inevercheckthis2002 (8/24/2016)
GilaMonster (8/23/2016)
If they're getting denied access to just one table, then there's a object-level permission set on that table. Check the DMVs for the details, you can then use REVOKE to remove that permission.EXEC sp_table_privileges
@table_name='MY_TABLE';
Returns zero rows
Odd, because it should return results. I have a table I just created, no explicit permissions and a user with database owner role running the query and I get this:
Can you check what the user in question has for permissions on the database, schema and table, explicitly what they've been granted or denied. Use a sysadmin user to check that, and query the DMVs.
Also, what fixed database roles does that user have?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thank you.
Runnning sp_table_privileges as a sys admin returned results similar to what Gail posted. Which doesn't show me table permissions, just who can grant permissions??? (right?)
I'm not sure what DMVs to query to get the info on table permissions.
-
inevercheckthis2002 (8/26/2016)[hr
Runnning sp_table_privileges as a sys admin returned results similar to what Gail posted. Which doesn't show me table permissions, just who can grant permissions??? (right?)
No, it shows the granted permissions on that table.
I'm not sure what DMVs to query to get the info on table permissions.
Not the DMVs, sorry. Start with sys.database_permissions and sys.database_principals, and then sys.database_role_members for the roles the user has
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply