question

sethuraman avatar image
sethuraman asked

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,

securitypermissionssql server 2008 r2
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

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

Oleg avatar image Oleg commented ·

@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 Likes 0 ·
sethuraman avatar image sethuraman commented ·

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 Likes 0 ·
Show more comments

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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