mssqltips logo

Unable to see a SQL Server table

By:   |   Updated: 2011-05-18   |   Comments (2)   |   Related: More > Security

Problem

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?

Solution

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.

CREATE TABLE EveryoneSeesMe (ID INT);
GO 
GRANT SELECT ON EveryoneSeesMe TO Everyone;
GO 
CREATE TABLE IAmHidden (ID INT);
GO

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';
GO 

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';
GO
SELECT * FROM sys.objects WHERE type = 'U';
GO 
REVERT;
GO 

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


Last Updated: 2011-05-18


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

Hi,

 

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 08, 2013 - 5:57:12 AM - Pink Back To Top
Going to put this artilce to good use now.


download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools