Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists

If you received below error while attaching a .mdf file in cluster environment please follow below steps to resolve the issue

ERROR
Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.

Cannot use file 'D:\MSSQL\Data\AttachDemoDatabase.mdf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.

Problem (Cause):
May be the contained file disk not added to the cluster resource
OR
Disk not added as SQL Server resource dependency
Check as below
Goto to failover cluster manager -->  Expand Roles --> select Role --> goto Resources Tab --> Right click on SQL Server properties and
In SQL Server properties window click on dependencies there you need to see disks information

 


Resolution:
SQL Stopping you to create Database on D drive which is not a dependency of the SQL Server. Dependency tell OS the order in which they can come online. The child resource must come online before an attempt is made to bring parent resource online in a cluster. And this chain is an important aspect of SQL Clustering. A resource group has several resources in it. All the resources in a single resource group act as a single group. They failover as a one unit; Thus providing high availability on the available nodes.
Need to add this new drive to the dependency chain for SQL Server Resource Group.
--> Goto Failover Cluster Manager

--> Under the cluster name select Role
--> In right hand pane Select the Resource Group that contain SQL Server Resources.
--> In bottom window  select Resource Tab
--> Under Other resources Right click on SQL Server as shown in below image

--> In properties window Click on Insert Button

--> On add a row select Share Disk D that stores you database data files (.mdf)
--> In next row add all other required share disk which will stores your database log files and your server database backups and tempdb files.

-->  Make Sure that all resources are required (there is an AND next to subsequent resources) then click OK

Now you are good to go to attach your .mdf file to create database with existing data file.

Error 8101 An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON in SQL Server

When you got below error while executing below SQL Statement

INSERT INTO dbo.tlbTargetTable SELECT * FROM dbo.tlbSrcTable

Msg 8101, Level 16, State 1, Line 19

An explicit value for the identity column in table 'dbo.tlbTargetTable' can only be specified when a column list is used and IDENTITY_INSERT is ON.

SET IDENTITY_INSERT dbo.tlbTargetTable ON

Go

INSERT INTO dbo.tlbTargetTable SELECT * FROM dbo.tlbSrcTable

Go

SET IDENTITY_INSERT dbo.tlbTargetTable OFF

Go

You will get below error message after switching on identity insert

Msg 8101, Level 16, State 1, Line 20

An explicit value for the identity column in table 'dbo. tlbTargetTable' can only be specified when a column list is used and IDENTITY_INSERT is ON.

You have to add column list for insert statement

SET IDENTITY_INSERT dbo.tlbTargetTable ON

Go

INSERT INTO dbo.tlbTargetTable (id, col1, col2, col3, col4, col5) SELECT id, col1, col2, col3, col4, col5 FROM dbo.tlbSrcTable

Go

SET IDENTITY_INSERT dbo.tlbTargetTable OFF

Go

About SQL Server Engine & Agent Service Accounts

Configure Service Accounts for all SQL Server services.

Microsoft recommends creating individual accounts for all services. If you use the same service account for every server, then when somebody repeatedly mistypes the service account password during an installation, you can lock out the service account, causing widespread havoc.

Do not make the service account a local administrator. As a best practices SQL Server service should be using a minimally privileged account. We should always avoid running SQL Server services under the contexts of the local system, local administrator or domain administrator accounts.

If you're installing multiple components, the recommendation is to use separate accounts. Example you have two services to be concerned with: the main SQL Server service and the SQL Server Agent service. Even in this case it's recommended to use separate accounts.

We need to make sure that the SQL Server service account has “full control” permissions on data, log and backup directories for read and write activities so need to grant the ‘Perform Volume Maintenance Tasks’ right to the account that will be used for the SQL Server service.

We need to use SQL Server Configuration manager to do any modifications for SQL Services. Account change and Password change both should be done only via configuration manager not services.msc.  If we update/change service account details through SQL Server configuration manager, it will perform additional configurations, such as  updating the Windows local security store which protects the service master key for the Database Engine etc.

Windows Domain Account Permissions

SQL DATABASE Engine Service account should have below permissions

·         Log on as a serive permissions

·         Replace a process-level token

·         Bypass traverse checking

·         Permission to start SQL Writer

·         Permission to read the Event Log service

·         Permission to read the Remote Procedure Call service

SQL AGENT Service account should have below permissions

·         Log on as a serive permissions

·         Replace a process-level token

·         Bypass traverse checking

·         Adjust memory quotas for a process

Unable to ALTER SQL Server database from single user to multi user

When i try to alter database from single user to multi user i got facing below error

Transaction (Process ID 233) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205)

Solution :



USE [MyDatabase]
GO
SET DEADLOCK_PRIORITY LOW
GO
ALTER DATABASE MyDatabase SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

Drop DEFAULT Constraint in SQL Server

Syntax to drop existing default constraint from a table

USE [DatabaseName]

GO

ALTER TABLE [Table_Name]
DROP CONSTRAINT [DefaultConstraintName]

Below is the example to drop default constrain from orderqty table

USE [MyDatabase]

GO

ALTER TABLE [dbo].[orderqty]
DROP CONSTRAINT [CK__orderqty__qty__628FA481]

Add Default constraint for existing table in SQL Server

Below is the syntax for adding default constraint for an existing table



ALTER TABLE tbl_Name
                       ADD CONSTRAINT default_columnName
                                         DEFAULT value FOR columnNmae

example :
Below is the query to create DEFAULT constraint on qty column when the orderqty table already created



ALTER TABLE orderqty
ADD CONSTRAINT default_qty
DEFAULT 1 FOR qty;


We created default constraint and we did not inserted value for qty field using INSERT statement in below example but "1" value inserted for qty

Cannot alter the user 'dbo'. (Microsoft SQL Server, Error: 15150)

Problem

Cannot drop the user 'dbo'.

Rename failed for User 'dbo'.  (Microsoft.SqlServer.Smo)

Cannot alter the user 'dbo'. (Microsoft SQL Server, Error: 15150)

Solution 

USE My_Database; 
ALTER AUTHORIZATION ON My_Database::DATABASE_NAME TO sa