SQL Server 2008 R2 question

Question

question

sethuraman asked Aug 1, '18

SQL Server 2008 R2 question

Hello,

I am working on a SQL Server 2008 R2 database with 2 accounts

1) user-withpermissions

when i execute the following command SELECT * FROM fn_my_permissions('<<username>>','Database') it has Connect, Select, Execute and View Definition.

2) user-withoutpermissions

when i execute SELECT * FROM fn_my_permissions('<<username>>','Database') it has Connect - only.

When i use the low privilege account via Power BI interface, i get to connect, see the list of tables and when i select one to view data get an error "Expression error - id column of the table is not found"

I believe the above is for restricted permissions, however what's confusing is when i execute a select * from query on the table i get all the resulting data.

any one with more expertise, can you guide me to understand what is going on.

Thanks,

security permissions sql server 2008 r2

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg commented · Aug 01, 2018 at 02:48 PM

@sethuraman When you call the my permissions function, you use 'Database' as a securable class parameter value so the function will not return any permissions for any specific objects. If the user without permissions can run select all from some table then it simply means that the said user actually does have a select permission on that table (granted either explicitly or inherited via food chain). You can run

select * from fn_my_permissions('someSchema.SomeTable', 'object')

to see any permissions for that table. The odds are that you will find that there is a granted permission.

0 ·

sethuraman commented · Aug 03, 2018 at 10:07 AM

Hello Oleg,

I did run the command on specific table object and all the columns in the table has a 'select' which explains why the select * from tsql is working.

However when selecting the table from powerbi, list of all tables is visible and when selecting the specific table it gives the below error

"Expression error - id column of the table is not found"

where id is the primary key. !! ??

any thoughts

0 ·

Oleg sethuraman commented · Aug 03, 2018 at 12:38 PM

@sethuraman When you run the command on the specific table, you use the account of the user in question right? In other words:

execute as login = 'UserInQuestionGoesHere';
select * from sys.fn_my_permissions(
    'dbo.SomeTable', 'object');
revert;
go

What I am trying to say is that the check for permissions for the specific table always runs in the context of whoever is running the query, so if you login to SSMS as yourself and just run the select from this function for a specific table, surely you get results, which include the rows with select permissions for all columns (these are your permissions). To check the permissions of the specific user for that table, you need to run the select as that user.

In any case, if you find out that the user does not have select permission to the table then it explains your problem. If the user does have permissions then the problem is with the actual query Power BI generates. You can start profiler trace and check to make sure that there is nothing wrong with the query.

The bottom line is that if the user has select permissions then the source of the query origination is irrelevant, T-SQL from SSMS or query from Power BI should return same data.

0 ·

question