New answers tagged logins
As mentioned by Aaron, you're login 'say' X would be pointing to a different database rather than NordenWeb, when it throws error with state 38: You need to check the login for any other mapped databases. To find correct error you need to enable the trace when above error is created: To dig in deep do nslookup 220.127.116.11 from windows to find the host ...
Below is an example to generate the needed script. DECLARE @SqlScript nvarchar(MAX); SELECT @SqlScript = (SELECT N'ALTER LOGIN ' + QUOTENAME(name) + N' WITH DEFAULT_DATABASE = tempdb;' FROM sys.server_principals WHERE default_database_name = 'master' AND type NOT IN('C', 'K') FOR XML PATH('')); EXEC sp_executesql @SqlScript;
You should never try to update system tables directly, in most cases it is not going to let you as you have found. In your case you will want to build out a dynamic statement for the ALTER LOGIN without knowing how many there are on the instance. However, you will need to be cautious in doing this to ensure you do not touch logins you shouldn't. This query ...
Connect to SQL Server using Windows authentication. Run sp_readerrorlog and see the complete message which would have come in errorlog when you tried connecting with SQL Authentication. This message is not of much help. You can update the question with message From error log: Login failed for user 'sa'. Reason: An attempt to login using SQL ...
Top 50 recent answers are included