SQL 2008 R2 is not let to login with one of login ID
in [Server Configuration]
|
Prev: The performance counter registry hive is corrupted
Next: Permissions Needed to Count SSB Transmission Queue Messages
From: Zee Aay on 17 Jun 2010 19:02 Hello, I have SQL 2008 R 180 installed as default instance on my laptop. Access is mixed mode When I log into my laptop as administrator, I can login using sa without any problem But when I login as a different User ( with admin rights) , SSMS does not let me log in using sa or Windows authentication. Any idea why would this be an issue
From: Erland Sommarskog on 18 Jun 2010 17:21 Zee Aay (viol-8-r(a)hotmail.com) writes: > I have SQL 2008 R 180 installed as default instance on my laptop. > Access is mixed mode > When I log into my laptop as administrator, I can login using sa without > any problem > > But when I login as a different User ( with admin rights) , SSMS does not > let me log in using sa or Windows authentication. > > Any idea why would this be an issue Obviously, if you are logged into windows as MACHINE\OTHERUSER, to be able to log in with Windows authentication, MACHINE\OTHERUSER must have been granted access to SQL Server, either directly or through a Windows group. Have you done this? On the other hand, you should always be able to log as in sa, no matter how you have logged into Windows. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Zee Aay on 18 Jun 2010 17:29 Hi Erland, This is why I am concerned. One, why it is not letting me to log in as 'sa' whereas it should and must but it does not Second, Yes, I have granted the other User access to SQL; even more then what it is required, just to let him in atleast once. "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9D9BEDA7E86F5Yazorman(a)127.0.0.1... > Zee Aay (viol-8-r(a)hotmail.com) writes: >> I have SQL 2008 R 180 installed as default instance on my laptop. >> Access is mixed mode >> When I log into my laptop as administrator, I can login using sa without >> any problem >> >> But when I login as a different User ( with admin rights) , SSMS does not >> let me log in using sa or Windows authentication. >> >> Any idea why would this be an issue > > Obviously, if you are logged into windows as MACHINE\OTHERUSER, to be > able to log in with Windows authentication, MACHINE\OTHERUSER must > have been granted access to SQL Server, either directly or through a > Windows group. Have you done this? > > On the other hand, you should always be able to log as in sa, no matter > how you have logged into Windows. > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >
From: Erland Sommarskog on 19 Jun 2010 06:20 Zee Aay (viol-8-r(a)hotmail.com) writes: > This is why I am concerned. > One, why it is not letting me to log in as 'sa' whereas it should and must > but it does not > Second, Yes, I have granted the other User access to SQL; even more then > what it is required, just to let him in atleast once. When logged in as the other user, run this from a command line: sqlcmd sqlcmd -U sa -P yourpasswordhere Post the error messages. Also look up the SQL Server error and post the error message for the login failures in this log. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Zee Aay on 19 Jun 2010 11:18
Hi Erland, Please find here below both messages when I type sqlcmd and sqlcms -U sa -P Hashaum95 Microsoft Windows [Version 6.1.7600] Copyright (c) 2009 Microsoft Corporation. All rights reserved. Microsoft Windows [Version 6.1.7600] Copyright (c) 2009 Microsoft Corporation. All rights reserved. C:\>sqlcmd HResult 0x274D, Level 16, State 1 TCP Provider: No connection could be made because the target machine actively re fused it. Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or in stance-specific error has occurred while establishing a connection to SQL Server .. Server is not found or not accessible. Check if instance name is correct and i f SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.. Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired. C:\> "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9D9C7D89C78AYazorman(a)127.0.0.1... > Zee Aay (viol-8-r(a)hotmail.com) writes: >> This is why I am concerned. >> One, why it is not letting me to log in as 'sa' whereas it should and >> must >> but it does not >> Second, Yes, I have granted the other User access to SQL; even more then >> what it is required, just to let him in atleast once. > Microsoft Windows [Version 6.1.7600] Copyright (c) 2009 Microsoft Corporation. All rights reserved. C:\Users\sarshad>cd.. C:\Users>cd.. C:\>sqlcmd HResult 0x274D, Level 16, State 1 TCP Provider: No connection could be made because the target machine actively re fused it. Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or in stance-specific error has occurred while establishing a connection to SQL Server .. Server is not found or not accessible. Check if instance name is correct and i f SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.. Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired. C:\>sqlcmd -U sa -P Hashaum95 HResult 0x274D, Level 16, State 1 TCP Provider: No connection could be made because the target machine actively re fused it. Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or in stance-specific error has occurred while establishing a connection to SQL Server .. Server is not found or not accessible. Check if instance name is correct and i f SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.. Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired. C:\> > When logged in as the other user, run this from a command line: > > sqlcmd > sqlcmd -U sa -P yourpasswordhere > > Post the error messages. Also look up the SQL Server error and post the > error message for the login failures in this log. > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > |