Solved

SQL logins

Posted on 2012-03-12
18
600 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

863 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

26 Experts available now in Live!

Get 1:1 Help Now