Cannot open database requested in login 'DBNAME'. Login fails.

• iwadhwa

  Grasshopper

  Points: 15

  More actions

  April 15, 2011 at 4:12 am

  #73019

  I am very new for .net and sql. The application which I am maintaining is running from last 3yrs. But we got the err

  Cannot open database requested in login 'dbname'. Login fails.

  Login failed for user 'sa'.

  Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

  When went to database and saw the properties it was in single user mode. When we restart the database automatically it went in multi user mode. Please explain me how it can happen?

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  April 15, 2011 at 2:07 pm

  #1312341

  To the best of my knowledge someone has to put a database into single user mode and the user must have ALTER database permissions.

  Of course since you are using sa to connect to the server everyone connecting using that application can do anything they want on the SQL Server.

  If you are on SQL Server 2005 or later you can query the default trace for Object:Altered events with an ObjectType = 16964. That can show you when and "who" did it". YOu can't know what the change is, but you can know that someone did an ALTER DATABASE. Of course the default trace rolls over after 100MB so you may not have the data anymore.

  Here's the query:

  With cteObjectTypes AS

  (

  SELECT

  TSV.trace_event_id,

  TSV.subclass_name,

  TSV.subclass_value

  FROM

  sys.trace_subclass_values AS TSV JOIN

  sys.trace_columns AS TC ON

  TSV.trace_column_id = TC.trace_column_id

  WHERE

  TC.[name] = 'ObjectType'

  ),

  cteEventSubClasses AS

  (

  SELECT

  TSV.trace_event_id,

  TSV.subclass_name,

  TSV.subclass_value

  FROM

  sys.trace_subclass_values AS TSV JOIN

  sys.trace_columns AS TC ON

  TSV.trace_column_id = TC.trace_column_id

  WHERE

  TC.[name] = 'EventSubClass'

  )

  SELECT

  TE.[name],

  I.ApplicationName,

  I.BigintData1,

  I.ClientProcessID,

  I.ColumnPermissions,

  I.DatabaseID,

  I.DatabaseName,

  I.DBUserName,

  I.Duration,

  I.EndTime,

  I.Error,

  I.EventSequence,

  Convert(nvarchar(10), I.EventSubClass) + N'-' + ESC.subclass_name as EventSubClass,

  I.FileName,

  I.HostName,

  I.IndexID,

  I.IntegerData,

  I.IsSystem,

  I.LineNumber,

  I.LoginName,

  I.LoginSid,

  I.NestLevel,

  I.NTDomainName,

  I.NTUserName,

  I.ObjectID,

  I.ObjectID2,

  I.ObjectName,

  Convert(nvarchar(10), I.ObjectType) + N'-' + OT.subclass_name as ObjectType,

  I.OwnerName,

  I.ParentName,

  I.Permissions,

  I.RequestID,

  I.RoleName,

  I.ServerName,

  I.SessionLoginName,

  I.Severity,

  I.SPID,

  I.StartTime,

  I.State,

  I.Success,

  I.TargetLoginName,

  I.TargetLoginSid,

  I.TargetUserName,

  I.TextData,

  I.TransactionID,

  I.Type,

  I.XactSequence

  FROM

  sys.traces T CROSS Apply

  sys.fn_trace_gettable(CASE WHEN CHARINDEX('_', T.[path]) <> 0

  THEN SUBSTRING(T.PATH, 1, CHARINDEX('_', T.[path]) - 1) + '.trc'

  ELSE T.[path]

  End, T.max_files) I JOIN

  sys.trace_events AS TE ON

  I.EventClass = TE.trace_event_id LEFT JOIN

  cteEventSubClasses AS ESC ON

  TE.trace_event_id = ESC.trace_event_id And

  I.EventSubClass = ESC.subclass_value LEFT JOIN

  cteObjectTypes AS OT ON

  TE.trace_event_id = OT.trace_event_id AND

  I.ObjectType = OT.subclass_value

  WHERE

  T.is_default = 1 AND

  TE.NAME = 'Object:Altered' AND

  I.ObjectType = 16964

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question