return correct table_schema

Question

return correct table_schema

J Oakland 21

There are 2 table schemas in the information_schema.columns table for a specific table. They're both base tables. None of the tables are listed as DENY in the sys.database_permissions. Only 1 of the table schemas are visible in SSMS. How do you determine from meta data which table schema is visible in SSMS with TSQL?

4 answers

Your answer

Answer 1

Michael Taylor 61,106

Can you provide some example data to back this? AFAIK whether someone can see a schema/table/whatever is completely based upon security permissions and has nothing to do with the informational views. But seeing an example might help clarify things, especially if you're talking about a system schema.

Answer 2

Tom Phillips 17,781

Normally SSMS displays all objects in the database.

Without your query or your results of the query, it is hard to guess as to your problem.

J Oakland 21 Reputation points

2022-09-07T21:08:13.333+00:00

select distinct TABLE_SCHEMA
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'Person'

select TABLE_SCHEMA
, TABLE_NAME
, TABLE_TYPE
from INFORMATION_SCHEMA.TABLES
where TABLE_NAME = 'Person'

![> select *

from sys.database_permissions
where state_desc = 'DENY']4
J Oakland 21 Reputation points

2022-09-07T21:09:18.083+00:00

This is for the last screenshot.

select *
from sys.database_permissions
where state_desc = 'DENY'
Tom Phillips 17,781 Reputation points

2022-09-08T14:16:34.763+00:00

In SSMS, under Tables expand FileTables and External Tables. Not all tables are listed under "Tables" folder.

There is a table in your database named dbo.Person, somewhere.
Erland Sommarskog 128.9K Reputation points MVP Volunteer Moderator

2022-09-08T20:53:42.95+00:00
Good point!

Run

SELECT type FROM sys.objects WHERE name = object_id('dbo.Person')

Answer 3

Erland Sommarskog 128.9K MVP Volunteer Moderator

Rather than looking for DENY, look for GRANT. To be able to see a table, a user needs to hold at least the permission VIEW DEFINITION (which is implied if you have SELECT permission). This example illustrates:

   CREATE TABLE T1 (a int NOT NULL)  
   CREATE TABLE T2 (a int NOT NULL)  
   CREATE USER UselUser WITHOUT LOGIN  
   GRANT SELECT ON T1 TO UselUser  
   go  
   EXECUTE AS USER = 'UselUser'  
   go  
   select TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE  
   from INFORMATION_SCHEMA.TABLES   
   go  
   REVERT  
   go  
   DROP TABLE T1, T2  
   DROP USER UselUser

J Oakland 21 Reputation points

2022-09-08T13:08:23.727+00:00

It looks like everything is granted. How do you select the table(s) that have access or not to select the accessible table from the INFORMATION_SCHEMA?
J Oakland 21 Reputation points

2022-09-13T10:19:42.657+00:00

I'm not trying to figure out how to grant or deny access. I need to know how to determine table(s)' access level programmatically.
Tom Phillips 17,781 Reputation points

2022-09-13T14:11:20.627+00:00

Determining a users "effective" rights programmatically is extremely complicated.

I suggest you start here:
https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/determining-effective-database-engine-permissions?view=sql-server-ver16

In your example, the user has access to both dbo.Person and Person.Person. SSMS requires a DENY right to remove it from the list in SSMS, and your query shows you do not have any DENY rights set.

Or this method using extended properties:
https://sqlstudies.com/2017/04/03/hiding-tables-in-ssms-object-explorer-using-extended-properties/

Answer 4

Erland Sommarskog 128.9K MVP Volunteer Moderator

I'm not trying to figure out how to grant or deny access. I need to know how to determine table(s)' access level programmatically.

To see whether a user has permission to an object, you can do this:

   EXECUTE AS USER = 'YourUser'  
   go  
   SELECT has_perms_by_name('dbo.YourTable', 'OBJECT', 'SELECT')  
   go  
   REVERT

Determining the permissions without impersonation can be very difficult, since permissions, both GRANT and DENY can come from many places.

Share via

return correct table_schema

4 answers

Your answer