October | 2016 | SQL with Manoj

SQL Error – Unable to open the physical file. Operating system error 5: “5(Access is denied.)”

October 25, 2016 2 comments

Today I Detached a Database from SQL Server and moved the mdf/ldf files to an another drive, but while Attaching those files from the new location I got error below by using following script:

USE [master]
GO

CREATE DATABASE [NewDatabase] ON 
( FILENAME = N'E:\NewDB\NewDatabase.mdf' ),
( FILENAME = N'E:\NewDB\NewDatabase_log.ldf' )
 FOR ATTACH
GO

Msg 5120, Level 16, State 101, Line 4
Unable to open the physical file “C:\NewDB\NewDatabase.mdf”. Operating system error 5: “5(Access is denied.)”.
Msg 1802, Level 16, State 7, Line 4
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Well the error clearly mentions that the error is due to the limited access of Service Account by which SQL Services are running, it doesn’t have access to the new folder location where the database mdf/ldf files are moved.

So, you just need to go to the new folder location, check the folder Properties and go to Securities tab, select the Service account and click on Edit button.

On the new Security popup window click under the Allow checkbox for Full Control permission.

Now execute the above Attach script and the Database will be attached without any issues.

Categories: SQL Errors Tags: Attach Database

Get SSRS Report Stats, Usage report and Runtime

October 12, 2016 Leave a comment

SELECT 
       Cat.[Name] AS ReportName, 
       Cat.[Path] AS ReportPath, 
       ELog.[Format] AS ReportFormat, 
       ELog.[UserName] AS RunBy, 
       ELog.[TimeStart] AS RunDateTime, 
       CONVERT(nvarchar(10), ELog.[TimeStart], 101) AS RunDate,  
       CASE(ELog.[Source])
              WHEN 1 THEN 'Live'
              WHEN 2 THEN 'Cache'
              WHEN 3 THEN 'Snapshot' 
              WHEN 4 THEN 'History'
              WHEN 5 THEN 'AdHoc'
              WHEN 6 THEN 'Session'
              WHEN 7 THEN 'Rdce'
              ELSE 'Unknown'
       END AS [DataSource],
       CASE(ELog.[RequestType])
              WHEN 0 THEN 'Interactive'
              WHEN 1 THEN 'Subscription'
              WHEN 2 THEN 'Refresh Cache'
              ELSE 'Unknown'
       END AS RequestType,
       1 AS [NumRuns], 
       ELog.[TimeDataRetrieval],
       ELog.[TimeProcessing],
       ELog.[TimeRendering],
       ELog.[ByteCount],
       ELog.[RowCount]
FROM ELogecutionLog AS ELog 
INNER JOIN [Catalog] AS Cat ON ELog.ReportID = Cat.ItemID 
WHERE Cat.Type = 2
AND ELog.[TimeStart] >= '2016-08-01'
ORDER BY ELog.TimeStart DESC