By: K. Brian Kelley | Comments (2) | Related: > 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:
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:
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
- Check out these security related tips:
About the author
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
