none
not able to add buildin admin as login ..sql server 2008 express edition RRS feed

  • Question

  • i have added my AD group in the local administration of the server ... but facing issue while adding a new login .. (SA is disabled on the instance)

    im getting below error 


    Microsoft Windows [Version 6.1.7600]
    Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

    C:\Users\myuser>sqlcmd -S servername\instancename
    1> CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[mas
    ter]
    2> GO
    Msg 15247, Level 16, State 1, Server servername\instancename, Line 1
    User does not have permission to perform this action.
    1>

    referred :: http://sqlserver-help.com/2012/02/08/help-i-lost-sa-password-and-no-one-has-system-administrator-sysadmin-permission-what-should-i-do/

    will putting the instance in single user mode .. make any difference to above method ? 

    Thursday, April 4, 2013 4:31 PM

Answers

  • Option 1: http://archive.msdn.microsoft.com/addselftosqlsysadmin/

                 download the cmd file from above link and save it onto the server .. where you want to add yourself to sysadmin ..

                 double click the cmd file and it will ask for instance name .. default is taken as the SQL express edition instance ...

                 only enter the instance name (example for instance node-1\SQL2012_INST .... we will just enter SQL2012_INST .. not the network name)

    below messages will appear in the cmd window .. (Note : it will cause SQL restart as it puts instance in single-user mode and restarts it twice )

    Adding 'NODE-1\test' to the 'sysadmin' role on SQL Server instance 'SQL2012_INST

    1'.
    Verify the 'MSSQL$SQL2012_INST1' service exists ...
    Stop the 'MSSQL$SQL2012_INST1' service ...
    The SQL Server (SQL2012_INST1) service is stopping.
    The SQL Server (SQL2012_INST1) service was stopped successfully.

    Start the 'MSSQL$SQL2012_INST1' service in maintenance mode ...
    Add 'NODE-1\test' to the 'sysadmin' role ...
    Stop the 'MSSQL$SQL2012_INST1' service ...
    The SQL Server (SQL2012_INST1) service is stopping...
    The SQL Server (SQL2012_INST1) service was stopped successfully.

    The SQL Server (SQL2012_INST1) service is starting.
    The SQL Server (SQL2012_INST1) service was started successfully.

    'NODE-1\test' was successfully added to the 'sysadmin' role.
    Press any key to continue . . .

    Option: 2  http://sqlserver-help.com/2012/02/08/help-i-lost-sa-password-and-no-one-has-system-administrator-sysadmin-permission-what-should-i-do/

    follow .. Balmukand's manual method to achieve the same .. :) 

    Tuesday, April 9, 2013 10:34 AM

All replies

  • are you an admin on the windows box?the idea is in single user mode,windows admin can get sysadmin permissions on sql server, even though they are not explicitly. and once you have sysadmin permissions, you can create/alter users and add sysadmin permissions to any login.

    thats why you need to start the server in single user mode and try creating the steps again.

    Okay, I just re-read your question, since you are admin on the windows box, set the sql server in single user mode and try.


    Hope it Helps!!



    • Edited by Stan210 Thursday, April 4, 2013 6:13 PM
    • Proposed as answer by Alex.Friedman Sunday, April 7, 2013 6:57 AM
    Thursday, April 4, 2013 6:09 PM
  • Hi Stan210,

                     Thanks for reply ..

                     i found one more useful link for the same http://archive.msdn.microsoft.com/addselftosqlsysadmin/

                     as this causes a restart .. will perform the same during off hours .. and share the results ..

    GS

    Friday, April 5, 2013 1:10 PM
  • don't forget to stop the other sql services too before launching SQL Server in single user mode. They count towards connections to SQL Server, so will use up your 1 admin connection


    Thanks, Andrew

    Friday, April 5, 2013 2:22 PM
  • Option 1: http://archive.msdn.microsoft.com/addselftosqlsysadmin/

                 download the cmd file from above link and save it onto the server .. where you want to add yourself to sysadmin ..

                 double click the cmd file and it will ask for instance name .. default is taken as the SQL express edition instance ...

                 only enter the instance name (example for instance node-1\SQL2012_INST .... we will just enter SQL2012_INST .. not the network name)

    below messages will appear in the cmd window .. (Note : it will cause SQL restart as it puts instance in single-user mode and restarts it twice )

    Adding 'NODE-1\test' to the 'sysadmin' role on SQL Server instance 'SQL2012_INST

    1'.
    Verify the 'MSSQL$SQL2012_INST1' service exists ...
    Stop the 'MSSQL$SQL2012_INST1' service ...
    The SQL Server (SQL2012_INST1) service is stopping.
    The SQL Server (SQL2012_INST1) service was stopped successfully.

    Start the 'MSSQL$SQL2012_INST1' service in maintenance mode ...
    Add 'NODE-1\test' to the 'sysadmin' role ...
    Stop the 'MSSQL$SQL2012_INST1' service ...
    The SQL Server (SQL2012_INST1) service is stopping...
    The SQL Server (SQL2012_INST1) service was stopped successfully.

    The SQL Server (SQL2012_INST1) service is starting.
    The SQL Server (SQL2012_INST1) service was started successfully.

    'NODE-1\test' was successfully added to the 'sysadmin' role.
    Press any key to continue . . .

    Option: 2  http://sqlserver-help.com/2012/02/08/help-i-lost-sa-password-and-no-one-has-system-administrator-sysadmin-permission-what-should-i-do/

    follow .. Balmukand's manual method to achieve the same .. :) 

    Tuesday, April 9, 2013 10:34 AM