FAQ: SQL Server login and permissions troubleshooting

Monitor failed logins, restrict account access or solve a permissions issue with these 10 SQL Server security FAQs.

If you're having security issues ranging from monitoring failed logins, restricting account access or even a permissions issue, this collection of frequently asked questions will help. Our experts at SearchSQLServer.com have addressed readers' common questions, which should assist you in performing tasks and resolving issues.

Frequently Asked Questions:

SQL Server login and permissions troubleshooting

  1. How can I monitor failed login attempts in SQL Server 2000?
  2. How can I prevent users having more than one login session open?
  3. How should I handle mismatched SQL Server logins?
  4. How should I convert logins afer a SQL Server 2005 upgrade?
  5. Is it possible to restrict sa access to SQL Server?
  6. How can I change the sa password without knowing the old password?
  7. How will I know when new user accounts are created?
  8. Why do applications use single logon to SQL Server?
  9. How can I troubleshoot an ASPNET permissions issue?
  10. What best practices should we follow for SQL Server 2005 developer permissions?

1. How can I monitor failed login attempts in SQL Server 2000?

Sadly, failed login error reporting is disabled by default (gasp!). I don't know why Microsoft has it setup that way, but it is. Pop into SQL Enterprise Manager, choose your server, go to properties, go to the Security tab and enable it. Once this has been activated, you'll start seeing them show up in your log files. A note of caution, the errors are notoriously brief and don't provide the amount of information that I would want if I was performing forensics on the server.
—Steven Andres, Security Expert

Return to SQL login and permissions FAQs

2. How can I prevent users having more than one login session open?

SQL Server is a data repository and not a full access control system. Your database application should be the one in charge of controlling access to the back-end data. One popular way that I've seen this performed is by having the application login using one set of credentials and then managing the user credentials inside your custom application. This way, when a user attempts to login concurrently, your application can query a "currently logged in" users list and display the appropriate error message. The user's password won't allow him into Query Analyzer so there's no harm in attempting a connection.
—Steven Andres, Security Expert

Return to SQL login and permissions FAQs

3. How should I handle mismatched SQL Server logins?

Can you explain the differences between Data Transformation Services (DTS) and SQL Server Integration Services (SSIS)?

SQL Server stores LOGIN information in the MASTER database and USER information in the user databases. Because of this, when you restore a database from a different server or if you attach a database from a different server, you will get mismatches unless the servers were set up and administered the same way.

If you are moving a database to a brand new server where no login information yet exists, you can script out login information and apply it to the new server. This can be done using DTS or it can be done by writing T-SQL code to script out the data. This MSDN article goes into more detail on how this can be accomplished.

Also refer to sp_change_users_login in SQL Server Books Online. This stored procedure gives you information on mismatches as well as allows you to fix the mismatched data.

To identify and clean up orphaned users, you can write T-SQL code that will identify the users that do not have a corresponding login and then use the sp_revokedbaccess stored procedure to drop them. Refer to this article for a completed solution.
—Greg Robidoux, Backup and Recovery Expert

Return to SQL login and permissions FAQs

4. How should I convert logins afer a SQL Server 2005 upgrade?

In SQL Server 7 and 2000, when you create an object owned by a user other than DBO, you have to qualify the object by owner name when accessing it, unless you are logged in as that user.

In SQL Server 2005, objects are no longer qualified by owner. Instead, they're qualified by schema name. This is something I covered in a recent SearchSQLServer.com tip on SQL Server 2005 permissions. In essence, this will result in less confusion regarding object qualification; you will be able to organize objects logically, rather than by who owns them.

As for server logins, I don't see a lot of potential issues. The same semantics apply in terms of what logins are to SQL Server and how they work. One difference you might want to be aware of is SQL Server 2005's ability to enforce password policies, which may mean that some of your users will have to change their passwords after your upgrade.
—Adam Machanic, SQL Server 2005 Expert

Return to SQL login and permissions FAQs

5. Is it possible to restrict sa access to SQL Server?

The System Administrator account, by definition, has access to everything at all times. You could reduce that access, but then you are changing the definition of the "SA" account and you should really have your application (or users) logging in as another username.
—Steven Andres, Security Expert

Return to SQL login and permissions FAQs

6. How can I change the sa password without knowing the old password?

Use OSQL with the –E option (for authenticated user). If you are an administrator of the OS, you will get in without being prompted for the sa password. Once you are at the OSQL interactive prompt, issue the following command:

EXEC sp_password NULL, 'newpassword', 'username' GO 
—Steven Andres, Security Expert

Return to SQL login and permissions FAQs

7. How will I know when new user accounts are created?

If you have logging enabled, you should be able to see the new account created. However, by default there is very little SQL logging enabled (a deficiency I hope is rectified in SQL 2005).
—Steven Andres, Security Expert

Return to SQL login and permissions FAQs

8. Why do applications use single logon to SQL Server?

Sometimes, there is a good reason for this. Some developers make their software so that it can be deployed on a number of database back-end solutions (MS SQL, MySQL, ORACLE, IBM DB2, Sybase, etc.). For this reason, they would rather write their authentication procedures once, in their compiled software, and be done with it.

Of course the "right" way to do it would be to have the database back-end manage authentication, but this turns into a support nightmare for the developer. Take it from someone who's been there -- if you do it the "right" way, you'll end up spending most of your valuable tech support engineers' time supporting MS SQL or ORACLE or DB2 issues, when they really should only be supporting the application itself. It is tempting to just say, "Well, that's a Microsoft/Oracle/IBM problem" if you're that tech support engineer. But in the end you must support your customer. This means that now all your support engineers have to become experts in five different database packages -- that's not going to happen!
—Steven Andres, Security Expert

Return to SQL login and permissions FAQs

9. How can I troubleshoot an ASPNET permissions issue?

I am writing a program with VB.NET to access some data from my SQL 2000 Server but it always complains of log in failed for user "myservernameaspnet." What could be causing this error and do you have a solution?

It sounds like you might have a permissions issue with the ASPNET user on your development box. A simple way of checking is to grant "full control" permissions to the "ASPNET" user in the directory where your data resides. If this works, you know you have a permissions issue. Start rolling back the permissions until it breaks again -- you want to have the minimum amount of permissions necessary for the program to run properly. If this doesn't work, try re-installing ASP.NET.
—Steven Andres, Security Expert

Return to SQL login and permissions FAQs

10. What best practices should we follow for SQL Server 2005 developer permissions?

There are a couple of Microsoft whitepapers that are an excellent resource for you:

Additional informative (non-Microsoft) guides are at:

—Steven Andres, Security Expert

Return to SQL login and permissions FAQs

Didn't find what you were looking for?

Pose a question to anyone of our SQL Server experts.

You can also browse our SQL Server Topics section for more advice.

This was first published in July 2006

Dig deeper on Microsoft SQL Server Performance Monitoring and Tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close