SQL Server Management Studio - Login question

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
PMH4514Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
jss1199Connect With a Mentor Commented:
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
 
vinurajrCommented:
u can set the access rights there in the security....
0
 
BrandonGalderisiCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
PMH4514Author Commented:
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
 
jss1199Commented:
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
 
PMH4514Author Commented:
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
 
jss1199Commented:
Examine the actual logins from Security\Logins (from the server level, not an individual database)
0
 
PMH4514Author Commented:
I see only BUILTIN\Users, sa, and my SQL Server login name I created for my client app.
0
 
PMH4514Author Commented:
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
 
PMH4514Author Commented:
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
 
jss1199Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.