can i query roles and users from my various tabular databases

Question

can i query roles and users from my various tabular databases

stan

Ten Centuries

Points: 1120
More actions
March 20, 2024 at 3:55 pm
Go to Answer
#4398934

hi we've begun recording apps x roles at my company. Currently the apps are erp's but i think roles attached to tabular models would blend nicely, especially if i can find a way to also expand the groups assigned to certain roles. i'd feed the data daily. from what i've seen AD has some kind of query capability but i couldnt get it too far in my installed AD client.
Lowell

SSC Guru

Points: 323522
More actions
March 21, 2024 at 1:42 pm
Answer
#4399515
if you are comfortable with PowerShell, this example command will get all end users, including those that are members via a group-within-a-group:

here i am assuming you found a group named "SSASTabularAccess" with current permissions, and want to know who's getting access.
```
Get-ADGroupMember -Identity "SSASTabularAccess" -Recursive | SELECT -property SamAccountName |Sort-Object -Property SamAccountName
```
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

stan Ten Centuries Points: 1120 More actions · Answer 1

thx lowell, by any chance do you also know how to get a list of members/groups from each role under a tabular model database? I might have asked too many questions in this post and can easily post this portion in a new post.

Chrissy321 SSCoach Points: 15785 More actions · Answer 2

This?

SELECT * FROM $System.TMSCHEMA_ROLES

SELECT * FROM #TMSCHEMA_ROLE_MEMBERSHIPS

This reply was modified 1 year, 2 months ago by Chrissy321. Reason: Added second select

stan Ten Centuries Points: 1120 More actions · Answer 3

thx chrissy321. Where would i run that? in the engine on the same server where ssas is? I dontg think t-sql ius a choice of language on the ssas instance. i get an error when i run that on the engine.

Chrissy321 SSCoach Points: 15785 More actions · Answer 4

You can issue an MDX query or a TSQL query against a linked server.

https://www.sqlshack.com/linking-relational-databases-with-olap-cube/

stan Ten Centuries Points: 1120 More actions · Answer 5

thank you chrissy321,

i read your link quickly and see if i have an mdx query, it would be sent native thru openquery probably via a new query from a db or the instance itself.

But i can also send thru openquery your t-sql looking querys?

Chrissy321 SSCoach Points: 15785 More actions · Answer 6

This is tsql

DROP TABLE IF EXISTS #Roles;

SELECT * INTO #Roles FROM OPENROWSET

(

'MSOLAP',

'Data Source=localhost;Initial Catalog=YourSSASDatabaseName;Provider=MSOLAP.4;Integrated Security=SSPI;Format=Tabular;',

'SELECT * FROM $System.TMSCHEMA_ROLES'

);

DROP TABLE IF EXISTS #RoleMemberships;

SELECT * INTO #RoleMemberships FROM OPENROWSET

(

'MSOLAP',

'Data Source=localhost;Initial Catalog=YourSSASDatabaseName;Provider=MSOLAP.4;Integrated Security=SSPI;Format=Tabular;',

'SELECT * FROM $System.TMSCHEMA_ROLE_MEMBERSHIPS'

);

SELECT * FROM #Roles

SELECT * FROM #RoleMemberships

stan Ten Centuries Points: 1120 More actions · Answer 7

thx, so those 2 selects are mdx. i was able to run them right on the ssas instance without a linked server. But i understand the value in having a linked server where more flexibility is possible. Now i have to decide whose post to mark as an answer. i believe only one answer is allowed per post on this forum. Chrissy321 i "liked" yours and marked lowell's as an answer. next time i'll separate posts better.

This reply was modified 1 year, 2 months ago by stan.

can i query roles and users from my various tabular databases

Cookies on SQLServerCentral