Solved

SQL logins

Posted on 2012-03-12
18
649 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

617 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