Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server Management Studio - Login question

Posted on 2009-04-06
11
Medium Priority
?
300 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 500 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

660 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