Unable to see a SQL Server table

By:   |   Comments (2)   |   Related: > Security


I'm a developer and in a given database I can't see a table I know is there. The reason I know it's there is I can execute a stored procedure which queries the table and I never get an error executing it. What's going on?


Starting with SQL Server 2005, Microsoft began restricting what objects you could see in the database based on how your permissions were set up. For instance, if you don't have any permissions for a particular table, and you don't have permissions at a higher level (like being a member of the db_owner fixed database role), you won't see the table. That's a change in behavior from SQL Server 2000, where if you had access to the database, you could see every object in it. Therefore, it's likely you don't have any permissions against the table in question. If that's the case, then SQL Server is functioning as designed.

We can see this with a simple example. I've performed several steps that you'll need to recreate in order to run the example. First, you'll need to create a SQL Server based login called NormalUser. Within your test database you'll need to create a role called Everyone. Add the NormalUser login to the database and make it a member of the Everyone role. After that, go into the database you're using and execute the following script.

GRANT SELECT ON EveryoneSeesMe TO Everyone;

Here we're creating two tables, one of which the Everyone role has SELECT permissions on. The other table, IAmHidden, we're not assigning any permissions to. Now, if I execute the following, I'll see both tables, because my account is a member of the sysadmin server role (and therefore a member of the db_owner role for the database):

SELECT * FROM sys.objects WHERE type = 'U';

Because I have high privileges, I see both tables, like so:

create a sql server based login called normaluser

If I want to see what a normal user sees, I'll need to execute the following:

EXECUTE AS LOGIN = 'NormalUser';
SELECT * FROM sys.objects WHERE type = 'U';

Instead of seeing both tables, I'll only see EveryoneSeesMe. That's because the NormalUser only has permissions against that table. It gets those permissions because it a member of the Everyone role you created. Since no one has permissions to see IAmHidden, the NormalUser isn't shown that table when it queries sys.objects. Even if there were permissions against IAmHidden, but none of those permissions matched up to NormalUser, the query would still only show this:

normaluser only has permission because it is a member of the eveyone role you created using sql server 2005

Therefore, if you're running on SQL Server 2005 or above and you can't see an object that you know is there, check with your DBA. Chances are you don't have any permissions for that object. If that's the case, then SQL Server is performing its role properly.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, June 20, 2013 - 6:09:24 AM - tongki Back To Top (25501)



I have a weird problem. I cant edit my record even i use SA as login name. Only this app, called BMD can edit it. Whats wrong ?

Whenever I edit (in SQLCMD Mode), it gives out error:

another user has modified the content of this table or view; the database row you are modfying no longer exist in the database.Database eror Microsoft ODBC SQL Server Driver [SQL Server] Property can not be updated or deleted. [BMD]


Thank you so much

Wednesday, May 8, 2013 - 5:57:12 AM - Pink Back To Top (23802)
Going to put this artilce to good use now.

get free sql tips
agree to terms