Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite
1

Hi, so recently when trying to fix another bug within my sql server 2008 R2 db I removed the BUILTIN\Administrators login from sysadmin. Now it will not let me change anything winthin the database. Also I do not have the SA account password to log in as that user to change/ add any permission.

Can anyone help me add the BUILTIN\Administrators login back into sysadmin or find a way to alter the database using my windows account?

share|improve this question

2 Answers

up vote 1 down vote accepted

To assign sysadmin, you have to do it while logged in as a sysadmin. From here,

Requires membership in the role to which the new member is being added.

which is basic escalation prevention.

You'll need to do one of two things:

  1. Log in as a sysadmin (Windows account or otherwise), or have someone else do this if there are other sysadmin level logins on the server.

  2. Restart the instance in single-user mode (which allows local administrators to log in as sysadmin automatically), fix the login, then restart in multi-user mode.

share|improve this answer
I get that i need to restart the instance in single-user mode but i dont know how to do that. I tried following the steps in the link but i had no luck please could you give me some instructions on how to do this. – Glen Robson Jul 23 '12 at 15:46
@Glen: Go into SQL Server Configuration Manager, select SQL Server Services in the tree, and go into Properties for the database engine service of interest. Then select the Advanced tab. Click the dropdown arrow on the Startup Parameters value, and add ;-m (semicolon, dash, m) to the end of the text. Click OK to exit the dialog box. Restart the service at this point, do the work to fix the login, then reverse this process to remove the startup parameter, and restart the service again. – Jon Seigel Jul 23 '12 at 15:53
If you're using 2012 management tools, the process is slightly different -- instead of the Advanced tab, use the Startup Parameters tab. – Jon Seigel Jul 23 '12 at 15:56
Thanks for your reply i managed to restart SQL Server in single-user mode however it is saying: Login failed for user 'DOMAIN\grobson'. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461) – Glen Robson Jul 23 '12 at 16:01
@Glen: Stop the SQL Agent service and try again. – Jon Seigel Jul 23 '12 at 16:03
show 7 more comments
up vote 7 down vote

Start the SQL Server service in single-user mode (by using the -m startup parameter). When you do this, all members of the local administrators group will have sysadmin rights. Provided you are a member of that group, you should be able to do whatever sysadmin actions you need.

Take a look at Database Engine Service Startup Options for documentation.

Edit: To start SQL Server in Single-User Mode, please see this documentation.

share|improve this answer
I do applologise but would you be able to run me through the steps for doing this task? – Glen Robson Jul 23 '12 at 15:24
1) stop sql server. 2) start sql server from command line with the -m switch. 3) connect to sql server from an account with local admin privs. 4) make changes (add local admins). 5) stop sql. 6) start sql normally. here's a link from the one that @Shark has already provided – swasheck Jul 23 '12 at 15:37
@swasheck Im sorry but i do not know how to start the sql server from a command line. – Glen Robson Jul 23 '12 at 15:39
@GlenRobson See my edit, that's a BOL reference to tell you how to do it. Like swasheck said, you just need to stop the service (also stop SQL Server Agent) and you can use the service switch to start it in single-user mode, or you can use the command line to start SQL Server. Either way is fine, but you'll need to ensure after you regain access and do what you need to that you revert back to multi-user mode (i.e. without the single-user switch). – Thomas Stringer Jul 23 '12 at 15:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.