vishweshwar


hi All,

I am working on some migration project.it is from AS 2000 to AS 2005.

I have to create a role in AS 2005.Is this one is similer functionality like in AS 2000.

Plz let me know.

I am using AMO for making connection i can able to connect and now i have to create a role,In my previous development we used to create a role in database then after in cube with the help of DSO.Could you please help on this.

if possible if you have any sample to create a role in AS 2005 with AMO please send to me.

Thanks in Advance.

vishu





Re: how to create a role in AS 2005 with AMO programmatically

Adrian Dumitrascu


Hi,

There is a similar discussion about creating roles with AMO in this thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1795829&SiteID=1

Adrian Dumitrascu






Re: how to create a role in AS 2005 with AMO programmatically

vishweshwar

Hi Adrian,

Thanks for u r quick response,I gone through the link and i checked with the code but it is throwing error finding the database it self.

could u please tell me what could be the problem.because i checked with the same code which u provided in the Post.

Thanks in advance.







Re: how to create a role in AS 2005 with AMO programmatically

Adrian Dumitrascu

If you get an exception in this line:

Database db = s.Databases["the database id"];

then you need to specify the ID of your database instead of the string "the database id". To get the ID of your database, use SQL Management Studio to see the database properties (right click on the Database -> Properties).

Or, if you know the Name of the database, but not the ID, you can use the GetByName method:

Database db = s.Databases.GetByName["the Name of your database"];

Adrian Dumitrascu





Re: how to create a role in AS 2005 with AMO programmatically

vishweshwar

Hi

I used below code.FYI.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.AnalysisServices

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()
'
'Connect to SSAS Instance
'-----------------------------------------------------
Dim MyServer As New Server
Dim MyConnectionString As String = "localhost"
MyServer.Connect(MyConnectionString)

'Get Reference to Database
'-----------------------------------------------------
Dim MyDatabase As Database = MyServer.Databases.GetByName("Adventure Works DW")

'Get Reference to Role
'-----------------------------------------------------
Dim MyRole As New Role = MyDatabase.Roles.GetByName("My Role")


'Add Users
'-----------------------------------------------------
Dim MyRoleMember As New RoleMember("TestDomain\TestUser1")
MyRole.Members.Add(MyRoleMember)
MyRole.Update()

'-----------------------------------------------------
MyDatabase.Update() 'This may be optional
MyServer.Disconnect()

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

In above code am getting the error in Dim MyDatabase As Database = MyServer.Databases.GetByName("Adventure Works DW") this line.

is the problem with above code or anything i missed out please let me know.

Thanks in advance.

Regards

vishu.







Re: how to create a role in AS 2005 with AMO programmatically

Adrian Dumitrascu

Is the code running under an account that has permissions to read the "Adventure Works DW" database Try to temporary add the account as a server admin or as a database admin (you can do that with SQL Management Studio, right click on the server item -> Properties -> Security tab).

If this was the problem (lack of read permissions) you will need to setup a database role for that account, with read metadata and also write permissions.

Adrian Dumitrascu





Re: how to create a role in AS 2005 with AMO programmatically

vishweshwar

Hi

Thanks for the reply,i tried with what you said.

But there i can add only my NT Id only i cant see the permissions for read metadata.

still am getting the same problem.






Re: how to create a role in AS 2005 with AMO programmatically

Adrian Dumitrascu

To temporary add the user as a server administrator, you only need to add it as a member in the Security tab of the server properties dialog in SQL Management Studio.

If that doesn't fix the problem, we need to double check 2 things:

1. make sure your code connects to the server you want

2. run SQL Management Studio under the account you run the code ('Run As' works), connect to the server from #1, right click on the Adventure Works DW database -> Properties and verify that the ID of the database is the one you use in the code

If in step #2 you do not see the Adventure Works database, we need to see if it's a permissions problem (although it should not be since you added the user as server admin) or you don't have the database on that server (connect with other users or check the data folder from "%ProgramFiles%\Microsoft SQL Server").

Adrian Dumitrascu