Solved

SQL logins

Posted on 2012-03-12
18
596 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
  • 7
  • 7
  • 3
  • +1
18 Comments
 
LVL 28

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
 
LVL 28

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 28

Expert Comment

by:sammySeltzer
ID: 37710882
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 28

Expert Comment

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

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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 28

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 28

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 28

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 500 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now