Granting SELECT permission on a object

I am not sure if this is an odd question, so please let me know if what I describe below is possible.

We have a server, which is configured by someone else and none of us are "added in it".

As of now I have only sa login in said server.

I am checking this via

SQLCMD -E -S ".\SQLServer"

and then by

SELECT name FROM sys.syslogins

And the output is

sa

I wanted to just run a SELECT statement against a table to see the value.

Question

Is there anything I can do at all?

I have tried DB Changing On and Trustworthy, but since I am no member at all my access is denied.

I just need to run the SELECT statement.

I wanted to just run a SELECT statement against a table to see the
value.


Question


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:

use your_db;
 select *
 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:

use your_db;
 select *
 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:

select *
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 sysadmin.

Then check your database roles like this:

select *
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 select user
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 sa login
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 sa

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 public role

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 sa.

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

UPDATE
I figured out another situation that can lead to what OP was seeing:

CONTAINED DATABASE

Starting with 2012, database can be configured as CONTAINED.

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 user,
still shows sa principal. Of course it shows no win login of a user as it just does not exists at all

StackExchange Database Administrators Q#185721, answer score: 6