Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL logins

Posted on 2012-03-12
18
Medium Priority
?
722 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 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
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.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

927 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