Solved

SQL Server Management Studio - Login question

Posted on 2009-04-06
11
295 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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
 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
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.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

839 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