Post reply

Problems with security in SSAS

  • March 29, 2010 at 10:03 am

    #146237

    I have security problems with connecting to SSAS from BIDS as well as SSMS.

    If I try to deploy a project from BIDS, I get the folowing error:

    <<Either the 'Mark-PC\Mark' user does not have permission to create a new object in 'MARK-PC\SQL2005A', or the object does not exist.>>

    This is my home PC and all servers are local. 'Mark-PC' is my PC name, 'Mark' is my user name and 'SQL2005A' is a SQL Server 2005 instance name.

    I tried to do somethhing in SSMS, I connected to Analysis Services, it shows 'MARK-PC\SQL2005A(Microsoft Analysis Server 9.00.1399.06 - Park-PC\Mark' next to white triangle in green circle icon, that means I connected to it, but when I try to create a new database there, I get an error:

    <<Either the 'Mark-PC\Mark user does not have permission to create a new object in 'MARK-PC\SQL2005A', or the object does not exist>>.

    Please help me to resolve this controversial issue. I have SQL Server 2005 Standard Edition on Windows 7 Professional.

    Thanks in advance.

  • March 29, 2010 at 12:52 pm

    #1141649

    Can you create a new DB by right-clicking on the Databases heading in SSMS and choosing New Database...?


    Rick Todd

  • March 29, 2010 at 8:23 pm

    #1141901

    No, I cannot. This is what I described in second part of my original post. If I try to create a new Analysis Services database in SSMS, I get an error:

    <<Either the 'Mark-PC\Mark user does not have permission to create a new object in 'MARK-PC\SQL2005A', or the object does not exist>>.

  • March 30, 2010 at 6:54 am

    #1142151

    Would appear your account isn't part of the SSAS administrators group? Is your account a member of the local Machine Admins group (ie the windows group)? By default, that group is a member of the instance admins.

    Assuming you're running an account with lowered permissions, you need to use an account with local admin perms, connect to the instance (using SSMS) and then add your other user account to the Admins on the instance. This gives you permissions to create databases etc.

    Steve.

  • March 30, 2010 at 9:02 am

    #1142312

    SSAS security is based on windows authentication only meaning security can only be assigned to local users and groups and domain users and groups; unlike the SQL Server database engine which contains a SQL authentication option.

    To setup SSAS security for management activities, there are two primary security roles:

    1) The server role (server admin), which provides access to the complete SSAS server (seems like its what you need)

    2) Database roles, which defines end-user access and database-level admin tasks

    As Steve explained you need to add yourself to the server-level role which is accessable through the server properties window of your analysis services instance and by selecting the security page.

    Thanks,

    costa

    MCTS: BI 2008, MCITP: BI 2008
    Stay Thirsty My Friends

  • March 30, 2010 at 9:23 am

    #1142346

    Here is what I tried to do to add myself to SSAS Admin group:

    In Object Explorer in SSMS I right-mouse clicked on my connection to SSAS, clicked Properties. In Analysis Server Property window I clicked Security in the left panel.

    In the right side I clicked <<Add...>> button, and Select Users and Groups dialog box popped-up. Here I clicked <<Object Type...>>, Selected Built-in security principals and Users check-boxes and clicked OK. Then clicked Advanced, Find Now, from the drop-down list I chose myself which is 'Mark' in Folder 'MARK-PC', then clicked OK, clicked OK once more, and 'Mark' appeared in NT Users and Groups box.

    I clicked OK on it, and here is the message:

    <<Either the 'Mark-PC\Mark' user does not have permission to alter the 'MARK-PC\SQL2005A' object, or the object does not exist>>..

    It sounds like I cannot add myself to SSAS Admin group. Do I have to change any settings in Control Panel? I just want to remind: this is Windows 7 Professional, I had XP before and never had such problems.

  • March 30, 2010 at 9:46 am

    #1142377

    You'll need to attack this from the Windows security side of things. Make sure you're an administrator on the computer itself, then try it again.


    Rick Todd

  • March 30, 2010 at 9:53 am

    #1142384

    This is what I also suspected, Rick. But excuse me for maybe stupid question: how can I check if I am administrator on my PC ? This is Windows 7 and I am very fresh in it.

    Thanks.

  • March 30, 2010 at 10:02 am

    #1142399

    B.T.W., I just checked my status in Control Panel -> User Accounts and Family Safety -> User Accounts. It shows that Mark is Administrator.

    I also have another idea: to install another instance of SQL Server and to carefully go through all the steps, perhaps I have missed something important in security settings when I initially installed it.

  • March 30, 2010 at 10:07 am

    #1142403

    I found that after I got over the initial UI shock (which I like, btw), many of the functions are the same in Windows 7 as XP, etc:

    Windows Explorer -> Right Click on My Computer -> Manage -> Configuration -> Local Users and Groups -> Groups -> Administrators -> Look for/Add appropriate user.

    You can also follow these instructions: http://windows.microsoft.com/en-US/windows-vista/Add-a-user-account-to-a-group

    Hope that helps


    Rick Todd

  • March 30, 2010 at 10:09 am

    #1142411

    Maybe it's the main thing I hate about the new versions of Windows: you have to run the app as administrator? No idea on that, but it's pretty easy to right click on the Management Studio icon and choose "run as administrator", to try that. If that works I would try adding your user to the SSAS administrators group, as posted earlier, so maybe you don't have to run as admin all the time.


    Rick Todd

  • March 30, 2010 at 11:52 am

    #1142518

    Go back to the server properties window. On the general tab page you will see a list of SSAS server properties. At the bottom of the page Select "Show Advanced (All) Properties check box". In the properties list scroll down to the "Security\" section and make sure the "Security\ BuiltinAdminsAreServerAdmins" value is set to "True". Restart the SSAS service and add yourself.

    MCTS: BI 2008, MCITP: BI 2008
    Stay Thirsty My Friends

  • March 30, 2010 at 2:55 pm

    #1142725

    Thanks all for your tips, now it works 50%. Here is what I've done so far:

    1. In Windows Explorer -> Manage -> Users and Groups I enabled Administrator.

    2. Logged off as Mark and logged in as Administrator.

    3. Started SSMS, and b.t.w, only now as Administrator I could see all this long list of properties, as mentioned by cos_ta.

    3. Added 'Mark' in security panel.

    4. Logged out as Administrator and logged in as Mark.

    5. Disabled Administrator.

    Now I can manually create SSAS databases in SSMS.

    However there are some problems with deploying projects from BIDS:

    1. For one project it generated bunch of security-related errors, however created database for a project along with all objects.

    2. For another project, it generated errors and did not create the database for it.

    Will investigate further, apparently I have to change something in Data Sources. Will keep you posted. Thanks.

  • June 16, 2010 at 4:54 pm

    #1181307

    Did it work after all?

    I face a similar problem!

    Any updates?

  • June 16, 2010 at 6:00 pm

    #1181322

    Theo: describe your problem and we'll see if we can help.

    Cheers,


    Rick Todd

Viewing 15 posts - 1 through 15 (of 24 total)

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