Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL logins

Posted on 2012-03-12
18
Medium Priority
?
697 Views
Last Modified: 2012-06-21
I've just setup sql server 2008 r2 and want to do the following:
1. chanage the "sa" password...
2. create three database admin users - what are the correct access settings to assign these users so they may have equal permissions of the "sa" account...or as close as possible?
0
Comment
Question by:fredb0319
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 3
  • +1
18 Comments
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 37710716
The best way I would do it is to give the users same admin that sa account has.

sa has sysadmin account.

The best way to find out, I think is to go to SSMS, expand security context.
Expand logins
Right-click on sa
click on server roles and also server mappings.

That kind of gives you idea.

Most important thing to know is that sa has sysadmin account and that's what you would need to give the user accounts you will be creating.
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 37710733
To change the sa password
OPen SQL Server Management Studio, Expand Security, and Click on the Logins folder.
Right click the sa account and select properties.
On the general page you can reset the sa password

http://blog.sqlauthority.com/2007/12/29/sql-server-change-password-of-sa-login-using-management-studio/

To create an admin account, right click the logins folder and select new login.
Give the account a name, change authentication to SQL Server authentication and set a  password.
Click Server Roles in the left hand pane and select sysadmin role.
Click OK
0
 

Author Comment

by:fredb0319
ID: 37710792
I've tried the suggestions but get the below response:
Change password failed for Login 'sa'. (Microsoft.SqlServer.Smo)
Additional information:
   An execption occurred while executing a Transact-SQL statemetn or batch.
   (Microsoft.SqlServer.Connectioninfo)
  Cannot alter the login 'sa' because it does not exist or you do not have permission.    (Microsoft SQL Server, error: 15151)
0
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
LVL 29

Expert Comment

by:sammySeltzer
ID: 37710839
If I were you, I would create an account with sysadmin role.

Then use that account to change sa password because at this point, you don't have the permission to do so.
0
 

Author Comment

by:fredb0319
ID: 37710852
sammySeltzer...I cannot create an account with sysadmin role without receiving the error I previously stated...
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 37710892
What account are you connection to the sql server with, he will need to be sysadmin role so you will need to connect as the sa user or another sysadmin account.
0
 

Author Comment

by:fredb0319
ID: 37710955
I still can't create a user or alter the 'sa' account without receiving the response below:
Change password failed for Login 'sa'. (Microsoft.SqlServer.Smo)
Additional information:
   An execption occurred while executing a Transact-SQL statemetn or batch.
   (Microsoft.SqlServer.Connectioninfo)
  Cannot alter the login 'sa' because it does not exist or you do not have permission.    (Microsoft SQL Server, error: 15151)
0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 37710970
Normally, the account that created the sql server database *should* allow you to create sql server account.
0
 

Author Comment

by:fredb0319
ID: 37710986
Agreed...but it is not allowing me to create a user without that response...I chose use windows authentication when I installed SQL...do I have to uninstall/reinstall and choose a different authentication?...
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 37711005
Change the authentication mode by right clicking your sql server in the left hand pane and choosing properties, go to the security page and change the server authentication to SQL Server and Windows authentication
0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 37711013
Just to be sure you are following protocol, are you using the first Security context?

As soon as you open SSMS object Explorer window, you see Security.

You expand you see Logins.

Are you starting there or are you starting by expanding database, your current DB and security?

And you always want to create accounts using Sql server authentication mode.
0
 

Author Comment

by:fredb0319
ID: 37711070
Yes...I am using the first security context...here is what I got when trying to change to SQL Server and Windows authentication:
TITLE: Microsoft SQL Server Management Studio
------------------------------

Alter failed for Server 'DATA-WAREHOUSE\DATA_WAREHOUSE'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

xp_regread() returned error 5, 'Access is denied.'
xp_regread() returned error 5, 'Access is denied.'
xp_regread() returned error 5, 'Access is denied.' (Microsoft SQL Server, Error: 22001)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=22001&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 37711123
I suppose you don't remember the sa password?

If you do, leave the authentication mode as is - which is windows.

Then log in as sa.
0
 

Author Comment

by:fredb0319
ID: 37711153
I have never known the 'sa' password...I didn't create it or set it up during installation...the installation was performed earlier today and didn't provide a field or ask for the 'sa' password...it is currently disabled for login...
0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 37711155
You can try this example on this link too.

Go all the way close to the bottom of the thread and look for one from Moby Disk.

http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/60b888bf-30dc-4eaf-bea8-5fd55181f8e5
0
 

Author Comment

by:fredb0319
ID: 37712009
Believe it or not I ran into the same problem once I figured everything out...I was connected  for single-user maintenance mode command-line and tried to create the "hero" user and it told me I didn't have enough priveleges...
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 37712850
It sounds like SQL Server was installed in Windows Authentication mode instead of mixed mode (SQL Server and Windows Authentication mode) so you do not have an sa login.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question