About Me

My photo
I know the last digit of PI

Thursday, September 15, 2011

Forget to add administrators group to SQL server 2008

Yep shit happens...
If you forget to add the administrator group (or at least the current user) to admin groups, and do not know the SA password then you have only two options
- reinstall SQL server
- use single-user-mode to add the accounts

Ok now the single-user-mode procedure.
0) login with Administrator (exactly the Administrator user, with another administrator account there will be problems)
1) open services.msc
2) go the "SQL Server (xxx)" service and stop it
3) open "Sql server configuration manager" from the start menu and make sure that the protocols "Shared memory", "Named pipes", "TCP/IP" are enabled
4) Go back to the services console right click "SQL Server (xxx)" and select properties and then enter "-m" as parameter. Click start
5) Now the server is started in single-user mode
6) Open command prompt and execute following command "sqlcmd -S \ -E", where -S specifies server and instance name, -E specifies to use windows credentials

sqlcmd -S MYMACHINE\SQLEXPRESS -E

7) Execute following commands for each windows account that you want to add

CREATE LOGIN [MYMACHINE\Administrator] FROM WINDOWS
GO
exec sp_addsrvrolemember @loginame='MYMACHINE\Administrator', @rolename = 'sysadmin'
GO

8) Stop the SQL service and restart it without -m parameter.
9) Now you are able to log-in with Administrator user and you do have administrator rights on SQL server

No comments: