"Login failed for user" error message when a user who does not have administrative credentials tries to gain access to an MSDE instance

Article translations Article translations
Article ID: 822502 - View products that this article applies to.
Expand all | Collapse all

On This Page

SYMPTOMS

If you install a Microsoft SQL Server 2000 Desktop Engine (MSDE) instance by using a Microsoft Windows Administrator user account, a user who does not have administrative credentials may not be able to gain access to the MSDE instance through Microsoft Visual Studio 2005 or Microsoft Visual Studio .NET Server Explorer.

When you try to gain access to the MSDE instance through Server Explorer, the SQL Server Login dialog box appears. If you select the Use Windows NT Integrated Security option in this dialog box to log on to the MSDE instance, you receive the following error message:
Login failed for user 'Domain Name\User Name'.
Note In this error message example, Domain Name and User Name are placeholders for the domain name and the user name of the Windows user account that you are using when you try to gain access to the MSDE instance.

WORKAROUND

To work around this problem, you can give logon rights for the MSDE instance to a Windows user account that does not have administrative credentials. You can then add this Windows user account to the System Administrators Group to give access rights for the user databases to this user account. To do this, follow these steps:
  1. Log on to the computer that is running the MSDE instance of Visual Studio 2005 or Visual Studio .NET. Log on as Administrator or as any other Windows user who has administrative credentials.
  2. Start Visual Studio 2005 or Visual Studio .NET.
  3. On the File menu, point to New, and then click Project.

    The New Project dialog box appears.
  4. Under Project Types, click Visual Basic Projects.

    Note In Visual Studio 2005, click Visual Basic in Project Types.
  5. Under Templates, click Windows Application, and then click OK. By default, Form1.vb is created.
  6. On View menu, click Code.
  7. Paste the following code as the first line in Form1.vb:
    Imports System.Data.SqlClient
  8. Paste the following code in the class definition of Form1 in Form1.vb:
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            ' Grant logon rights.
            RunProc("<Computer Name>", "master", "sp_grantlogin", "@loginame", "<Domain Name>\<User Name>")
    
            ' Add the user account to the System Administrators Group.
            RunProc("<Computer Name>", "master", "sp_addsrvrolemember", "@rolename", "sysadmin", "@loginame", _
    "<Domain Name>\<User Name>")
        End Sub
    
        Private Sub RunProc(ByVal HostName As String, ByVal DbName As String, ByVal CmdName As String, ByVal Param1Name _
     As String, ByVal Param1Value As Object, Optional ByVal Param2Name As String = vbNullString, Optional ByVal Param2Value _
     As Object = Nothing)
            Dim cn As New SqlConnection("Data Source=" & HostName & "\vsdotnet;Database=" & DbName &  _ 
    "; Integrated Security=True")
            Dim cmd As New SqlCommand(CmdName, cn)
            Dim param1 As New SqlParameter(Param1Name, Param1Value)
            Dim param2 As New SqlParameter(Param2Name, Param2Value)
    
            ' Open the connection.
            cn.Open()
    
            ' Set CommandType to StoredProcedure.
            cmd.CommandType = CommandType.StoredProcedure
    
            ' Add parameters.
            cmd.Parameters.Add(param1)
            If Param2Name <> vbNullString Then
                cmd.Parameters.Add(param2)
            End If
    
            ' Run the command.
            cmd.ExecuteNonQuery()
    
            ' Close the connection.
            cn.Close()
        End Sub
    Note Replace <Computer Name> in the code with the name of the computer that is running the MSDE instance. Replace <Domain Name> and <User Name> with the domain name and the user name of the Windows user account that you want to give access rights for the user databases to.
  9. On Debug menu, click Start to run the project.

STATUS

This behavior is by design.

MORE INFORMATION

To gain access to an MSDE instance from Server Explorer, you can do one of the following:
  • Provide Microsoft SQL Server user credentials.
  • Use the Use Windows NT Integrated Security option to provide the Windows user credentials.
When a MSDE instance is installed, SQL Server users are not automatically created for the instance. Therefore, you can only use the Use Windows NT Integrated Security option to log on to the MSDE instance from Visual Studio .NET.

Steps to Reproduce the Behavior

  1. Log on to your computer by using the Administrator account or any other user account that has administrative credentials.
  2. Install Visual Studio 2005 or Visual Studio .NET with MSDE from the Visual Studio 2005 or Visual Studio .NET Installation CD-ROM.
  3. Complete the installation process for the MSDE instance.
  4. Log off from your computer.
  5. Log on to your computer by using a Windows user account that does not have administrative credentials.
  6. Start Visual Studio 2005 or Visual Studio .NET.
  7. On the View menu, click Server Explorer.
  8. In Server Explorer, expand Servers, and then expand the Computer Name node where Computer Name is the name of your computer.
  9. Expand SQL Servers, and then double-click SQL Server Name\VSDOTNET where SQL Server Name is the name of the server computer where the databases are located.
  10. In the SQL Server Login dialog box, click to select the Use Windows NT Integrated Security check box.
  11. Click OK.

REFERENCES

For more information, click the following article number to view the article in the Microsoft Knowledge Base:
316649 How to use the Server Explorer in Visual Studio .NET and Visual Studio 2005

Properties

Article ID: 822502 - Last Review: May 2, 2006 - Revision: 2.3
APPLIES TO
  • Microsoft Visual Studio 2005 Standard Edition
  • Microsoft Visual Studio 2005 Professional Edition
  • Microsoft Visual Studio .NET 2003 Enterprise Architect
  • Microsoft Visual Studio .NET 2003 Enterprise Developer
  • Microsoft Visual Studio .NET 2003 Academic Edition
  • Microsoft Visual Studio .NET 2002 Professional Edition
  • Microsoft Visual Studio .NET 2002 Enterprise Architect
  • Microsoft Visual Studio .NET 2002 Enterprise Developer
  • Microsoft Visual Studio .NET 2002 Academic Edition
  • Microsoft SQL Server 2000 Desktop Engine (Windows)
Keywords: 
kbvs2005applies kbvs2005swept kbsysadmin kbserver kbdatabase kbprb KB822502

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com