CODEWITHSUNDEEP
sql-serversql-server-2008-r2
svager
svager

Reputation: 779

Create new user for local instance of SQL Server

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

Upvotes: 2

Views: 51242

Answers (3)

Bacon Bits
Bacon Bits

Reputation: 32170

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.

Upvotes: 0

delta911turbo
delta911turbo

Reputation: 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.

Upvotes: -1

steoleary
steoleary

Reputation: 9278

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

Upvotes: 2

Related Questions