I wanted to just run a SELECT statement against a table to see the
Is there anything I can do at all?
To find your permissions on the object you can use the function sys.fn_my_permissions
Just run this query to figure out your permissions on that table:
from sys.fn_my_permissions ('your_table_name', 'object')
If you have no
SELECT permission on the object, maybe at least you have
SELECT permission on subset of its columns.
In any case you can check your database permissions this way:
from sys.fn_my_permissions (null, 'database')
If you are fortunate and see
CONTROL permission among them, there is a possibility to re-grant missing permission to you.
It can be even this situation: you are
db_owner of this database, but someone used explicit
DENY on this table to your user.
Some db_owners underestimate the power of being
db_owner, so don't give up at the first
The SELECT permission was denied on ...
and continue to explore what permissions you have on the server and in the database of interest.
First, check out your server level permissions like this:
from sys.fn_my_permissions(null, 'server');
I think you have nothing interesting at the server level, at least from what you said it seems you lack
VIEW ANY DEFINITION permission and of course you are not
Then check your database roles like this:
from sys. user_token;
This gives you not only the
roles you are member of, but it gives you all the
users under which you can reach this database.
It can be usefull in case you are Windows login that is a member of many Windows groups mapped to this database, because simple
will return your win account (we are talking about
Windows Authentication now).
I think this is all I can suggest you in your situation. Explore what permissions you have, what roles you are member of, and then may be it will be possible to gain the access on your table
Second supplementary part
And now I want to give my version of how it can be that OP sees only
while he is not
sa and don't see any other login while logged in as a Windows principal
Yes it's true that compatibility view
syslogins does not show all security principals: at least it does not show fixed server roles.
But it has no problem with Windows principals, I controlled it on SQL Server 2014 as well.
So how it can be possible that OP sees only
sa when querying
syslogins logged in as Windows principal? Why doesn't he see his own win login, be it win account or just a win group, BUILTIN\Users or smth like this?
The simple answer is maybe OP has mistaken and did see another login but told us he was seeing only
The other and the only answer that I can give is this:
there is simply NO WIN USER mapped to this server
This situation can be reproduced only on versions
2005/2008/2008 R2 by granting
CONNECT SQL to
So here is the scenario:
We are on
SQL Server 2008 R2 and someone granted
CONNECT SQL to
public server role.
Well, in this case ALL THE ACTIVE DIRECTORY can log on to SQL Server without having no corresponding login at all!
Here in the picture the Windows principal
hp2\Mary was successfully logged on my local instance (instance need not to be local, it's just a repro on my notebook) without having any corresponding login at all.
Mary is not a member of
BUILTIN\Administrators (maybe it was even better drop that login at all to have purer repro), and what she see as logins is only
And executing OP's code she's got only
sa as well
Just to prove that there is no fake and my local 2008 R2 instance is full of logins and does not have only
sa I attach the whole screenshot of my notebook with 2 SSMS the first one started under sysadmin Anna and another one under login without permissions and even without mapping: Mary
I figured out another situation that can lead to what OP was seeing:
Starting with 2012, database can be configured as
In this case database, not a server, can authenticate a user.
The user will see only the database where it was created and (surprise!)
syslogins view, that in my opinion should be unaccessible at all to the
sa principal. Of course it shows no win login of a user as it just does not exists at all