2

I am attempting to set up a development machine. One of the components is SQL Server 2008 R2.

I log in into the machine as local administrator and then I am trying to use windows authentication to log in into local instance of SQL Server. When I try to change password for sa I get error:

Change password failed for Login 'sa'. (Microsoft.SqlServer.Smo)

Cannot alter the login 'sa', because it does not exist or you do not have permission. (.Net SqlClient Data Provider)

When I attempt to create a regular user. I get error that I don't have permissions to create user.

Create failed for Login 'bob'. (Microsoft.SqlServer.Smo)

User does not have permission to perform this action. (Microsoft SQL Server, Error: 15247)

My understanding was that all I needed to do is to log in as Local Admin in Windows to be able to administer SQL Server.

Please advise how can create users or change sa password.

Thank you

Improve this question

3 Answers 3

Reset to default
2

When you installed SQL server, what account did you set as the administrator for the instance? Usually you are asked if you would like to set a specific user or group to be sysadmin, the exception is SQL express where it automatically adds the local administrators group.

I think it defaults to the user that you used to run setup, so if that is different to the account you are signing in with now, use that one, otherwise, if you can't get in as sysadmin, you will need to follow this procedure:

Connect to SQL Server When System Administrators Are Locked Out

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

This article is for SQL Server 2012. Unfortunately it doesn't tell me how to use Config Manager for SQL Server 2008
Click the other versions dropdown box on the article and it will change to the 2008 version
0

My understanding was that all I needed to do is to log in as Local Admin in Windows to be able to administer SQL Server.

Just a note since @steoleary's answer is what you need to do, but but beginning with SQL Server 2008, Microsoft has not defaulted the BUILTIN\Administrators group to being in the sysadmin database role. This question discusses the same issue.

Improve this answer

Comments

-1

What I found, based on the information that steoleary mentioned, was that the original account that I setup as the administrator account for the SQL server was not the account that I was logged into the computer with and due to the way the SQL Server Manager Studio (2014) restricts you to whichever account launches the application. You will either need to log into the computer with that SQL Admin account to complete the create user step or run the Manager Studio as the SQL Admin. To run the Manager Studio as the SQL Admin you can just (shift+right click) on the application and run as a differntthe SQL Administrator.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.