SQL SERVER – Fixing Backup Error – Operating system error 5(Access is denied.) – SQL in Sixty Seconds #077

This error has to be one of the most famous error. I have installed SQL Server quite a many times, but I keep on getting this error once in a while. This is also one of the most searched error online. I have previously blogged about this but today, I planned to create a small video on the same subject. In this video I demonstrate how one can resolve the permissions issue with this error.

ere are few other blog posts related to this error, which I have written earlier.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER -Fix Error – Cannot open backup device. Operating system error 5(Access is denied.)

This has to be the most popular error in taking database backups. There are many reasons for this error and there are different ways to resolve it. I recently faced this error and for a while I was taking backup and found a very interesting solution to the problem. The error was as mentioned below:

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘d:\Log\aw.bak’. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Personally, I know this error happens when

  1. There is no directory with Log
  2. There is not enough space on drive d:\
  3. There is not enough permissions on d:\Log drive

In my case all of the above were not the reason for above error. The error was there because of the file was read only. There was already another backup file which existed in the folder with the name aw.bak and it was marked as read only. Due to the same reason, my backup was failing. Once I removed the checkbox for reading only, the backup was successful.

Here are few other blog posts related to this error, which I have written earlier.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – FIX: Error 5161 – An Unexpected file id was Encountered

Recently I was planning to give a demo about increasing tempdb files and its performance benefit. So while making the demo at home, I was doing multi-tasking. Talking to my daughter, having food and making demo. So, I ran the script and restarted SQL Server service but SQL Service didn’t start.

I looked into the ERRORLOG (and I would suggest you to look at that log in case of any SQL startup issues) and found below errors before SQL shutdown messages.

2014-12-02 17:03:24.42 spid18s     Error: 5161, Severity: 16, State: 1.

2014-12-02 17:03:24.42 spid18s     An unexpected file id was encountered. File id 3 was expected but 4 was read from “D:\TempDB\tempdev3.ndf”. Verify that files are mapped correctly in sys.master_files. ALTER DATABASE can be used to correct the mappings.

2014-12-02 17:03:24.42 spid18s     Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.

2014-12-02 17:03:24.93 spid18s     SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.

Since error message before shutdown were talking about tempDB database, I knew that I might have done something wrong while doing multi-tasking. I went back to query windows and read it completely. Initially I added files on C drive (first two commands) but later realized that I should not keep them on C as I already have space issues there, so I moved them using MODIFY FILE (last two commands)

USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2',
FILENAME = N'C:\TempDB\tempdev2.ndf', SIZE = 1024MB, FILEGROWTH = 0)
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3',
FILENAME = N'C:\TempDB\tempdev3.ndf', SIZE = 1024MB, FILEGROWTH = 0)
GO

-- Space issues on C, moving them to D
ALTER DATABASE [tempdb] MODIFY FILE
( NAME = tempdev2, FILENAME = N'D:\TempDB\tempdev3.ndf', SIZE = 10MB, FILEGROWTH = 0)
GO
ALTER DATABASE [tempdb] MODIFY FILE
( NAME = tempdev3, FILENAME = N'D:\TempDB\tempdev3.ndf', SIZE = 10MB, FILEGROWTH = 0)
GO

I called my daughter to check and she told me that she can see tempdev3 many times. Bummer! The second last command should have said tempdev2. mdf

I understood the problem but SQL is not getting started now, how can I modify it? Well, the easiest option is removed and add the file with a proper name

1. Start SQL via trace flag 3608

From the command prompt, we can use

net start mssqlserver /T3608

My machine has a default instance, that’s why I have used MSSQLServer. For named instance, we need to use MSSQL$<InstanceName>

If we open ERRORLOG, we should see below

Recovering only master database because traceflag 3608 was specified. This is an informational message only. No user action is required.

2. Run ALTER Database

Run ALTER DATABASE command as below to remove unwanted files. I have connected via SQLCMD and I am removing Tempdev2 because that has file as tempdev3. This can be done from Management Studio as well.

We can also run below query to find the current mapping. Database ID = 2 is always tempDB database.

SELECT name, physical_name FROM sys.master_files WHERE database_id = 2

In my case I got below output before removal.

NAME                PHYSICAL_NAME
-------------       -------------------------------
tempdev             E:\...\tempdb.mdf
templog             E:\...\templog.ldf
tempdev2            E:\TempDB\tempdev3.ndf
tempdev3            E:\TempDB\tempdev3.ndf

Based on the output you receive, you have to make appropriate changes.

3. Stop SQL Service and start normally.

Once above steps are performed, we should be able to connect to SQL and make changes to TempDB correctly this time.

Have you ever encountered similar tempdb errors? How did you fix them?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – FIX – The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet

It’s always a fun to learn and share new thing which I learn about any technology. So I decided to spend my weekend at home quietly, so I could learn some scripting using PowerShell and SQL Server. My laptop has tons of software installed so I always use my Virtual Machine to learn new things because I want to see how the things work when someone is working as a learner. My day didn’t start well as I was hit by errors as soon as I started Invoke-SQLCMD. This is part of PowerShell command and is worth a look.

This is the first error I got as soon as I wanted to run Invoke-SQLCMD

Error # 1 The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

The Error was because of the fact that I just installed SQL Server Engine on the machine. I didn’t install any client components. So essentially the error appears because the Invoke-sqlcmd cmdlet is not included as part of Windows PowerShell, but instead it is of sqlps (SQL Server 2008 PowerShell Host). So, before using invoke-sqlcmd we should install SSMS or the SQL Server Feature Pack (latest is SQL 2014 which can be downloaded from here)

Once installation was done, I ran the command and I got a new error.

Error # 2The ‘invoke-sqlcmd‘ command was found in the module ‘SQLPS’, but the module could not be loaded. For more information, run ‘Import-Module SQLPS’.

Here is the complete error message

As we can see above, I can also run Import-Module SQLPS to know more. Here is the output of the command

Error # 3 Import-Module : File E:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170.

Here is the complete error message

Why this error? If you know the basics of PowerShell, every command is called cmdlet (pronounced as command let). Invoke-SQLCmd is also a cmdlet provided either by the snap-in SqlServerCmdletSnapin100 (prior to SQL Server 2012) or module SQLPS (SQL Server 2012+). So, we need any one loaded into PowerShell (for example, at the beginning of your script) before you can call the cmdlet.

Assuming PowerShell is installed on the SQL server. You can open the Windows PowerShell Command prompt as below and get on to SQL power shell environment.

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

Once I followed above, I was able to use Invoke-SQLCMD as below

Hope this would help others in finding the solution to the errors. How many of you here are frequent users of PowerShell to administer and automate SQL Server tasks?
Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Fix : Msg 230, Level 14, State 1 – The SELECT permission was denied on the object, database, schema – Part II

A couple of days back I wrote the blog on Msg 230 and thought it was one of the simplest implementations of access control on the system. To continue with the code in this blog, please make sure you read the same first before getting into the examples here.

In that blog, I wrote a statement where I said the DENY takes precedence over GRANT. Post this, my good friend Vinod Kumar had pinged me to say that this might not be the case always. Now that statement got me thinking harder and I was quick to ask, prove me wrong? More than a challenge, it was a great opportunity to learn some of these finer details that lets us learn and understand SQL Server better. I have always been of the opinion that the best way to learn something is by sharing. So here is the repro to the exception where the DENY at a higher level is overridden by a GRANT at a granular level.

Similar to our previous post, let us go ahead and create our samples database, create a user inside this database, our secure table with some data.

CREATE DATABASE PermissionsDB
GO
USE PermissionsDB
GO
CREATE USER Pinal WITHOUT LOGIN;
GO
CREATE TABLE SecureTbl (ID INT, Name VARCHAR(50), SSN VARCHAR(20))
GO
INSERT INTO SecureTbl VALUES (1, 'Pinal', '111-22-3333')
GO

If we try to access the table using the login of “Pinal” at this point in time without any explicit permission given to the user. We will get the following error:

Msg 229, Level 14, State 5, Line 38
The SELECT permission was denied on the object 'SecureTbl', database 'PermissionsDB', schema 'dbo'.

Now that we have our secure table ready, let us go ahead and DENY explicit rights on the table to user ‘Pinal’. Next, we will go ahead and GRANT at a specific column level – in the command below. In this example, we have gone ahead to give rights on the column “ID” alone.

-- Does a GRANT at column-level override a DENY at the object level
DENY SELECT ON SecureTbl TO Pinal
GRANT SELECT (ID) ON SecureTbl TO Pinal
GO

Let us next change the user context to “Pinal” and execute the Select command.

EXECUTE AS USER='Pinal'
SELECT * FROM SecureTbl
SELECT ID FROM SecureTbl
REVERT

Though we have DENY permissions at the Object level (table in this instance), the GRANT seems to be working on the specific column level though.

Msg 230, Level 14, State 1, Line 38
The SELECT permission was denied on the column 'Name' of the object 'SecureTbl', database 'PermissionsDB', schema 'dbo'.
Msg 230, Level 14, State 1, Line 38
The SELECT permission was denied on the column 'SSN' of the object 'SecureTbl', database 'PermissionsDB', schema 'dbo'.

If you want to GRANT access to multiple columns, we can do it using the command as shown below.

GRANT SELECT (ID, Name) ON SecureTbl TO Pinal
GO

I am always of the opinion that it is better to DENY across the board and then give specific access sometimes. If you might notice, when compared to the previous article – we have NOT GRANTED SELECT at the global level for all here. We were explicit in the DENY logic for instance.

-- Explicitly grant at object level
GRANT SELECT ON SecureTbl TO PUBLIC

Let us make sure there are no unnecessary databases in our server. Here is the cleanup script.

-- Clean up
USE MASTER
GO
DROP DATABASE PermissionsDB

I hope you also learnt something new today. This is a great way to learn the fine prints when it comes to working with databases and permissions. Please let me know if you have ever used this technique in your environments and what was the actual scenario?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Fix – Msg 230, Level 14, State 1 – The SELECT permission was denied on the column of the object , database , schema

Being a DBA is one of the most rewarding experience because you control the server as well as the way the permissions are given to each and every individual users. In my career I have often seen people try to underestimate the use of GRANT, DENY and REVOKE. Not so much for the third part, but the first two are super important and super critical.

In this blog post, let me take you through a common requirement that I hear from application developers and business users about how to secure the environment. Let us take a simple scenario to illustrate the basics of GRANT and DENY in this article.

Assume you have a table inside your database which has sensitive personally identifiable information like SSN. Your organization mandates that no one should be able to read the SSN column but if they read the other columns, it is absolutely fine. These kind of requirements are all over the place and I have seen developers struggle to implement the same.

Here is my simple shot at the scenario. Let us create our database and the table having the SSN field:

CREATE DATABASE PermissionsDB
GO
USE PermissionsDB
GO
CREATE USER Pinal WITHOUT LOGIN;
GO
CREATE TABLE SecureTbl (ID INT, Name VARCHAR(50), SSN VARCHAR(20))
GO
INSERT INTO SecureTbl VALUES (1, 'Pinal', '111-22-3333')
GO

Additionally, I have created a user called ‘Pinal’ who needs to be denied permission. Now that we got our object under question, I am going to do two simple steps, a) First GRANT SELECT rights to everyone using this database. This can be restricted to the specific user account too. b) Explicitly we are going to DENY access to the SSN column.

-- Explicitly grant at object level
GRANT SELECT ON SecureTbl TO PUBLIC
-- DENY at the column level
DENY SELECT (SSN) ON SecureTbl TO Pinal
GO

The next step for us if to check if the user context of ‘Pinal’ as SELECT privileges on the table. To illustrate this, we are changing the user context using the EXECUTE AS command and trying to access the table.

-- Switch the context
EXECUTE AS USER='Pinal'
-- below would give Error as we are selecting SSN too
SELECT * FROM SecureTbl
-- No Error from below as SSN column is not in select list
SELECT ID, Name FROM SecureTbl
REVERT

In the above query, the first SELECT statement will result in the below error. This is because we have explicitly DENIED permission to user ‘Pinal’ from accessing the SSN field. The error also mentions this clearly.

Msg 230, Level 14, State 1, Line 21
The SELECT permission was denied on the column 'SSN' of the object 'SecureTbl', database 'PermissionsDB', schema 'dbo'.

Hence for all practical purposes, even though we have explicitly given permission to read on the Table Object, the DENY makes sure we will not be able to read the column values. This is one of the simplest way to look at GRANT and DENY implementation inside SQL Server.

Do let me know if you have used this technique inside your environments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – FIX – Msg 4864, Level 16, State 1 – Bulk load data conversion error

Working with SQL Server is such rewarding and most of the times, I get a chance to revisit and explore more into some of these errors. Long time ago, I had written a blog post to read data from CSV/text file and insert into SQL Server Table using BULK INSERT command. You can read it here: SQL SERVER – Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server

There have been many comments on that blog with error received by my readers. This blog post is to fix few errors mentioned over there.

Msg 4860, Level 16, State 1

This is one of the most common error reported by readers. Let’s have a look at the steps to reproduce the same:

CREATE TABLE library_books_loan
(
student_id VARCHAR(10) NOT NULL,
book_id    VARCHAR(10) NOT NULL,
branch_id  SMALLINT NOT NULL,
id_no      VARCHAR(10) NOT NULL,
date_out   DATE NULL DEFAULT NULL,
due_date   DATE NULL DEFAULT NULL,
date_in    DATE NULL DEFAULT NULL
)

Here is the sample text file I want to insert into this table:

student_id,book_id,branch_id,id_no,date_out,due_date,date_in<>
1,0399147020,1,9019,2013-11-22,2013-12-06,2013-12-01<>
2,0030059380,4,9007,2013-12-01,2013-12-15,2013-12-16<>
3,0671880756,5,9018,2013-12-08,2013-12-22,2013-12-22<>
4,0911625291,3,9013,2014-01-02,2014-01-16,2014-01-12<>
5,0688161995,5,9022,2014-02-10,2014-02-24,2014-03-01<>
6,0911625291,2,9011,2014-03-03,2014-03-17,2014-03-16<>
7,1861003730,3,9034,2014-04-17,2014-05-01,NULL<>
12,0911625607,2,9018,2014-04-19,2014-05-03,NULL<>

We can save the file as C:\Temp\Books_Library.txt. Here is the command which we will run to import the data.

BULK INSERT library_books_loan
FROM 'C:\Temp\Books_Library.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '<>',
FIRSTROW=2

The common mistake which is done by the user is, file gets saved locally (on their computer) and command is executed on remote instance of SQL Server. Here is the error which you would receive if you do this.

Msg 4860, Level 16, State 1, Line 12
Cannot bulk load. The file "C:\Temp\Books_Library.txt.txt" does not exist. 

So please make sure that file exists on the machine where SQL Server is running and path is correct on server itself.

Msg 4864, Level 16, State 1

If file is saved correctly on the server and BULK INSERT is tried then we are likely to get the below error messages:

Msg 4864, Level 16, State 1, Line 12
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 7 (date_in).
Msg 4864, Level 16, State 1, Line 12
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 7 (date_in).

If we read error message correctly, it is complaining about row 8 and row 9. Column is date_in. If we look back at data, we can see that NULL is provided as a value.

This means that SQL Server is treating that value as a string “NULL” and trying to insert that into a column which is defined as the date.

Here is the simple explanation by the demo.

CREATE TABLE SQLAuth (d DATE)
GO
INSERT INTO SQLAuth VALUES ('NULL')
GO

Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.

Solution

Modify the text file and don’t pass any value to the column. It would be treated as NULL automatically. Here is the modified version

student_id,book_id,branch_id,id_no,date_out,due_date,date_in<>
 1,0399147020,1,9019,2013-11-22,2013-12-06,2013-12-01<>
 2,0030059380,4,9007,2013-12-01,2013-12-15,2013-12-16<>
 3,0671880756,5,9018,2013-12-08,2013-12-22,2013-12-22<>
 4,0911625291,3,9013,2014-01-02,2014-01-16,2014-01-12<>
 5,0688161995,5,9022,2014-02-10,2014-02-24,2014-03-01<>
 6,0911625291,2,9011,2014-03-03,2014-03-17,2014-03-16<>
 7,1861003730,3,9034,2014-04-17,2014-05-01,<>
 12,0911625607,2,9018,2014-04-19,2014-05-03,<>

If we run the same command now, data should be inserted. As highlighted, we can see NULL values inserted on our destination table.

These are some common errors one can get using BULK INSERT command. In case you are facing other errors, please comment and I shall try to respond it in a future post for sure.

Reference: Pinal Dave (http://blog.sqlauthority.com)