Solved

SQL Server Management Studio - Login question

Posted on 2009-04-06
11
292 Views
Last Modified: 2012-08-14
I know when I installed SQL Server 2005 that I chose "mixed mode authentication". My client code specifies a username and password as defined in SQL Server security, so it does not need windows login. I do not want users of the workstations to be able to run SSMS unless they know the SQL Server login.  How do I change my configuration so that when one tries to load SSMS, their windows login does not grant them access? I want them to have to enter a username and password as defined in the server security. I looked in the server security properties and I can choose windows login, or SQL Server AND windows.. but I don't see any option for SQL Server only?  Or do I have to somehow do it at the user level in windows?

thanks
0
Comment
Question by:PMH4514
11 Comments
 
LVL 8

Expert Comment

by:vinurajr
Comment Utility
u can set the access rights there in the security....
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
There is no longer support for SQL Only Authentication.  It is either Windows only or Mixed.

Database access is limited by default and unless a user is explicitly given access they have none.  There are a few qualifiers to add with that.  Any user that is a local admin on the box will have access as SYSADMIN unless explicitly taken away.  If you have opened up your database so that public has access to everything, then you may have to explicitly add groups to the database server and deny access.
0
 
LVL 19

Accepted Solution

by:
jss1199 earned 125 total points
Comment Utility
You can't remove Windows authentication. You can only remove SQL authentication.

To disable people from Logging into SQL with their Windows password, remove any logons from SQL that are tied to a Windows account.  If these users are administrators, they are liekly gaining access to SQL because SQL, by default, allows all local administrators access - you can remove this capability by removing Builtin\Administrators from all SQL roles (or deleting it)
0
 

Author Comment

by:PMH4514
Comment Utility
BrandonGalderisi wrote:
>>Any user that is a local admin on the box will have access as SYSADMIN unless explicitly taken away.

can you describe the steps to take those rights away?

jss1199:
>>you can remove this capability by removing Builtin\Administrators from all SQL roles (or deleting it)
I went into the Security tab of my test sever and removed BUILTIN\Administrators from the list of logins. I then restarted the service, closed SSMS, and reopened it. (I'm logged onto windows with an account that has local admin rights).   It still passes me right through into the SSMS having specified "windows authentication"

0
 
LVL 19

Expert Comment

by:jss1199
Comment Utility
Then there are other SQL logins that have rights.  You will need to go through the list of security accounts in SQL and remove those tied to a Windows logon or group.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:PMH4514
Comment Utility
I see several roles under Security\Server Roles.. How do I know which is tied to a windows account? I can double click on any of them, and none lists any role members.
0
 
LVL 19

Expert Comment

by:jss1199
Comment Utility
Examine the actual logins from Security\Logins (from the server level, not an individual database)
0
 

Author Comment

by:PMH4514
Comment Utility
I see only BUILTIN\Users, sa, and my SQL Server login name I created for my client app.
0
 

Author Comment

by:PMH4514
Comment Utility
oh wait I see. On BUILTIN\Users I disallowed connect to DB engine. Now my windows login, even an admin, can't access anything inside SSMS, but Ic an login with the SQL Server username/password and have full access.

0
 

Author Comment

by:PMH4514
Comment Utility
I have one computer whose computer name is SALES-03..

Why in it's security/logins tab do I see:

SALES-03\QLServer2005MSFTEUser$SALES-03$SQLEXPRESS
SALES-03\QLServer2005MSSQLUser$SALES-03$SQLEXPRESS

What are those users for?
0
 
LVL 19

Expert Comment

by:jss1199
Comment Utility
There are two groups by those names on the computer SALES-03 (view Users and Groups on that computer and double-click the above names to view membership).  Any users that are contained in those two groups will have the rights specified in SQL server for those groups.
 
Glad you were able to solve your initial question by removing BUILTIN/Administrators and BUILTIN/Users
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

743 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

18 Experts available now in Live!

Get 1:1 Help Now