Solved

SQL Server Management Studio - Login question

Posted on 2009-04-06
11
293 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
ID: 24079898
u can set the access rights there in the security....
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24079909
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
ID: 24079911
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
ID: 24079980
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
ID: 24080001
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
Zoho SalesIQ

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

 

Author Comment

by:PMH4514
ID: 24080065
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
ID: 24080112
Examine the actual logins from Security\Logins (from the server level, not an individual database)
0
 

Author Comment

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

Author Comment

by:PMH4514
ID: 24080181
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
ID: 24080297
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
ID: 24080653
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

920 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

16 Experts available now in Live!

Get 1:1 Help Now