Post reply

Limited Permissions after SQL Express install on SQLExpress Server

  • April 30, 2009 at 2:52 am

    #204080

    HI folks,

    Hope someone can help.

    I've recently had SQL Server express installed on my machine at work. However, I'm not an administrator. I tried to set the server up so I was sysadmin. i can create the login but when I attempt to change the server role I get a permission error.

    I asked my IT dept to do this and they're having the same problem. They login to my machine with their account, add me as a login but can't change the server role, it's saying they don't have permissions to do this.

    They then add themselves as a login. Again, they receive a permissions error when attempting to add themselves as sysadmin. Perhaps they have limited permissions?

    I'm obviously missing something here but don't know what.

    Please can someone help me? Perhaps this has happened to you?

    It's SQL Server express 2005 running from Vista.

    Thanks

  • April 30, 2009 at 3:33 am

    #986515

    Only a sysadmin can give a sysadmin role.

    If you have set missed authentication while installation you can login using windows authentication abd give sysadmin role to users.

    Checkout

    http://support.microsoft.com/kb/937682

    You can run this query to know sysadmins in your server

    --WHO ARE THE SYSADMINS

    SELECT rol.name, mem.name,mem.type_desc

    FROM sys.server_role_members AS srm

    INNER JOIN sys.server_principals AS rol ON rol.principal_id = srm.role_principal_id

    INNER JOIN sys.server_principals AS mem ON mem.principal_id = srm.member_principal_id

    WHERE rol.name = 'sysadmin'

    Tanx 😀

  • April 30, 2009 at 3:57 am

    #986521

    I really appreciate the help. I'll look into it and get back to you to let you know how I get on.

    Cheers

    Paul

  • May 17, 2009 at 4:12 am

    #995955

    Have you read this,

    http://support.microsoft.com/kb/929907

    Add a user as an Administrator in the Surface Area Configuration

    or use RunAs to open SQL Server Management Studio.

  • May 18, 2009 at 2:42 am

    #996180

    Thanks, I'll check that article out for the future... I ended up re-installing and choosing mixed authentication..

    Cheers for the help

    Paul

Viewing 5 posts - 1 through 4 (of 4 total)

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