Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Issues removing server administrators from SSAS Expand / Collapse
Author
Message
Posted Sunday, April 6, 2014 11:40 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 14, 2014 6:53 AM
Points: 550, Visits: 1,061
Hi,
We've migrated our SSAS servers to a new Domain.
I now need to remove the old logins (from the previous domain) from SSAS as administrators.
I remove them from the security Tab, but they keep coming back.... :-(

The problem is, I can't add the new users to the security group while the old users are still there.
I get this error when I do try to add the new users while the old users are still there:

The following system error occurred: The trust relationship between the primary domain and the trusted domain failed.


I've scripted the action from SSAS to remove the old users & to add myself in:

<Batch xmlns='http://schemas.microsoft.com/analysisservices/2003/engine' Transaction='true'>
<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object />
<ObjectDefinition>
<Server xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200">
<ID>ServerName</ID>
<Name>ServerName</Name>
</Server>
</ObjectDefinition>
</Alter>
<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<RoleID>Administrators</RoleID>
</Object>
<ObjectDefinition>
<Role xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200">
<ID>Administrators</ID>
<Name>Administrators</Name>
<Members>
<Member>
<Name>wandragk</Name>
</Member>
</Members>
</Role>
</ObjectDefinition>
</Alter>
</Batch>

The output that I get:
Executing the query ...
<LockList> <Lock> <Type>Commit Read</Type> <LockStatus>Acquired</LockStatus> <Object> <ServerID>MSSQLServerOLAPService</ServerID> </Object> <ObjectID>F7602A9B-6B3D-4F52-B587-A19B6C01B1F6</ObjectID> </Lock> <Lock> <Type>Commit Read</Type> <LockStatus>Acquired</LockStatus> <Object> <ServerID>MSSQLServerOLAPService\Proxy</ServerID> </Object> <ObjectID>6C266DEC-0B3B-4C6C-8FD9-3C03CB7EDF35</ObjectID> </Lock> </LockList>
<LockList> <Lock> <Type>Commit Read</Type> <LockStatus>Released</LockStatus> <Object> <ServerID>MSSQLServerOLAPService</ServerID> </Object> <ObjectID>F7602A9B-6B3D-4F52-B587-A19B6C01B1F6</ObjectID> <Duration>0</Duration> </Lock> </LockList>
<LockList> <Lock> <Type>Commit Read</Type> <LockStatus>Released</LockStatus> <Object> <ServerID>MSSQLServerOLAPService\Proxy</ServerID> </Object> <ObjectID>6C266DEC-0B3B-4C6C-8FD9-3C03CB7EDF35</ObjectID> <Duration>0</Duration> </Lock> </LockList>
<LockList> <Lock> <Type>Write</Type> <LockStatus>Acquired</LockStatus> <Object/> <ObjectID>672AFF53-0ED5-4848-ABC8-610C0E3A396C</ObjectID> </Lock> </LockList>
<LockList> <Lock> <Type>Commit Read</Type> <LockStatus>Acquired</LockStatus> <Object> <ServerID>MSSQLServerOLAPService</ServerID> </Object> <ObjectID>F7602A9B-6B3D-4F52-B587-A19B6C01B1F6</ObjectID> </Lock> <Lock> <Type>Commit Read</Type> <LockStatus>Acquired</LockStatus> <Object> <ServerID>MSSQLServerOLAPService\Proxy</ServerID> </Object> <ObjectID>6C266DEC-0B3B-4C6C-8FD9-3C03CB7EDF35</ObjectID> </Lock> </LockList>
<LockList> <Lock> <Type>Commit Read</Type> <LockStatus>Released</LockStatus> <Object> <ServerID>MSSQLServerOLAPService</ServerID> </Object> <ObjectID>F7602A9B-6B3D-4F52-B587-A19B6C01B1F6</ObjectID> <Duration>0</Duration> </Lock> </LockList>
<LockList> <Lock> <Type>Commit Read</Type> <LockStatus>Released</LockStatus> <Object> <ServerID>MSSQLServerOLAPService\Proxy</ServerID> </Object> <ObjectID>6C266DEC-0B3B-4C6C-8FD9-3C03CB7EDF35</ObjectID> <Duration>0</Duration> </Lock> </LockList>

<LockList> <Lock> <Type>Commit Read</Type> <LockStatus>Acquired</LockStatus> <Object> <ServerID>MSSQLServerOLAPService</ServerID> </Object> <ObjectID>F7602A9B-6B3D-4F52-B587-A19B6C01B1F6</ObjectID> </Lock> <Lock> <Type>Commit Read</Type> <LockStatus>Acquired</LockStatus> <Object> <ServerID>MSSQLServerOLAPService\Proxy</ServerID> </Object> <ObjectID>6C266DEC-0B3B-4C6C-8FD9-3C03CB7EDF35</ObjectID> </Lock> </LockList>
<LockList> <Lock> <Type>Commit Read</Type> <LockStatus>Released</LockStatus> <Object> <ServerID>MSSQLServerOLAPService</ServerID> </Object> <ObjectID>F7602A9B-6B3D-4F52-B587-A19B6C01B1F6</ObjectID> <Duration>0</Duration> </Lock> </LockList>
<LockList> <Lock> <Type>Commit Read</Type> <LockStatus>Released</LockStatus> <Object> <ServerID>MSSQLServerOLAPService\Proxy</ServerID> </Object> <ObjectID>6C266DEC-0B3B-4C6C-8FD9-3C03CB7EDF35</ObjectID> <Duration>0</Duration> </Lock> </LockList>
<LockList> <Lock> <Type>Commit Write</Type> <LockStatus>Acquired</LockStatus> <Object> <ServerID>MSSQLServerOLAPService</ServerID> </Object> <ObjectID>F7602A9B-6B3D-4F52-B587-A19B6C01B1F6</ObjectID> </Lock> <Lock> <Type>Commit Write</Type> <LockStatus>Acquired</LockStatus> <Object> <ServerID>MSSQLServerOLAPService\Proxy</ServerID> </Object> <ObjectID>6C266DEC-0B3B-4C6C-8FD9-3C03CB7EDF35</ObjectID> </Lock> </LockList>
Administrators
Execution complete



And in the Results pane:

<return xmlns="urn:schemas-microsoft-com:xml-analysis">
<results xmlns="http://schemas.microsoft.com/analysisservices/2003/xmla-multipleresults">
<root xmlns="urn:schemas-microsoft-com:xml-analysis:empty" />
<root xmlns="urn:schemas-microsoft-com:xml-analysis:empty" />
</results>
</return>

Like I've mentioned before - once I look at the security within SSAS, the old accounts are back & new account is not added. :-(

Any help with this will be great!

thanks
Post #1558915
Posted Monday, April 7, 2014 12:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 13,299, Visits: 10,164
If you script out the entire cube as an ALTER statement and you search on the logins, are they there?
If they are, remove them from the script and run the script.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1558921
Posted Monday, April 7, 2014 12:24 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 14, 2014 6:53 AM
Points: 550, Visits: 1,061
Koen Verbeeck (4/7/2014)
If you script out the entire cube as an ALTER statement and you search on the logins, are they there?
If they are, remove them from the script and run the script.

Hi,
The problem is, is that I haven't even looked at the database level yet.

This is on the SSAS server security level....

I've tried to do the same now on the databases - same issue.
I will try the alter statement per DB to see if I can get the database roles sorted at least.
Post #1558925
Posted Monday, April 7, 2014 12:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 13,299, Visits: 10,164
Wandrag (4/7/2014)
Koen Verbeeck (4/7/2014)
If you script out the entire cube as an ALTER statement and you search on the logins, are they there?
If they are, remove them from the script and run the script.

Hi,
The problem is, is that I haven't even looked at the database level yet.

This is on the SSAS server security level....

I've tried to do the same now on the databases - same issue.
I will try the alter statement per DB to see if I can get the database roles sorted at least.


Right, I was mistaken between the server level and the database level.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1558927
Posted Monday, April 7, 2014 2:15 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 14, 2014 6:53 AM
Points: 550, Visits: 1,061
What will happen if I run a "repair" installation on the server?

How will this affect my SSRS & SQL engine?
Is there a way that I can run a repair on SSAS only?
Post #1558947
Posted Monday, April 7, 2014 2:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 13,299, Visits: 10,164
You can do a repair through the command line and specify your SSAS instance. (haven't tried it myself. I see in the article you can specify an instance, but I don't know if you can use the /FEATURES parameter to specify SSAS)
Not sure if it will solve the problem.

Repair a Failed SQL Server 2012 Installation




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1558951
Posted Monday, April 7, 2014 5:11 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 14, 2014 6:53 AM
Points: 550, Visits: 1,061
Hi,
Found the problem.

I've looked at the event log and saw this message:

The file master.vmp could not be deleted. Please check the file for permissions.

The new service account didn't had access to the path of the master.vmp file.
Once I've gave the account access it worked.

It's just very weird that one doesn't get any errors/notifications when one execute the script to add the users....
Post #1559000
Posted Monday, April 7, 2014 5:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 13,299, Visits: 10,164
Wandrag (4/7/2014)
Hi,
Found the problem.

I've looked at the event log and saw this message:

The file master.vmp could not be deleted. Please check the file for permissions.

The new service account didn't had access to the path of the master.vmp file.
Once I've gave the account access it worked.

It's just very weird that one doesn't get any errors/notifications when one execute the script to add the users....


Good to know, thanks for posting the solution.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1559005
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse