Monday, November 23, 2015

Change compatibility level



Using below statements you can change comparability level of  all user databases  with a single click. Comparability level is decided bases on sql server product level.

SQL Server
Comparability Level
SQL Version
SQL 2016
130

SQL 2014
120
12
SQL 2012
110
11
SQL 2008
100
10
SQL 2005
90
9
SQL 2000
80
8


use master;
go

DECLARE @SQLVer varchar(10);
-- select the compatibility level based in sql server version
select @SQLVer = CASE  SUBSTRING(convert(varchar,SERVERPROPERTY('productversion')),1,2)
when 12 then '120'
when 11 then '110'
when 10 then '100'
when 9. then '90'
when 8. then '80'
end


DECLARE UserDatabases_CTE_Cursor Cursor
FOR

-- filer user database names from sysdatabases
select name as DatabaseName from sys.sysdatabases where ([dbid] > 4)

OPEN UserDatabases_CTE_Cursor
DECLARE @dbName varchar(100);
DECLARE @compatQuery varchar(500);

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName
While (@@FETCH_STATUS <> -1)

BEGIN

-- set database compatibility level
set @compatQuery =  'ALTER DATABASE [' + @dbName + '] SET COMPATIBILITY_LEVEL = ' + @SQLVer



-- Execute compatability script
EXEC (@compatQuery)

-- Get next database
Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName
END

CLOSE UserDatabases_CTE_Cursor
DEALLOCATE UserDatabases_CTE_Cursor

GO

Tuesday, November 17, 2015

xp_cmdshell help; The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.


Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

Execute below statements to enable "xp_cmdshell" and 

EXEC sp_configure 'xp_cmdshell', 1  
GO  
RECONFIGURE  
GO


After executing above statements if you are facing below error

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.

Try below statements to enable advanced options  

EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
EXEC sp_configure 'xp_cmdshell', 1;  
GO  
RECONFIGURE;  
GO

Friday, November 6, 2015

Change password in remote session


How to change the password in a Remote Session when you connect the machine through Citrix or VDI 

Goto Remote session 

Press

CTRL + ALT +END 


you can find change password option.  

Click on change Password, 

Provide 
                  user name, current password, New password, confirm password  

And press symbol -->  in confirm password text box 

Wednesday, November 4, 2015

Get SQL Server Service Account using T-SQL



Identify the SQL Server Service Account using T-SQL 
You can find the service account information in Services 
Go to Start --> Run --> Services.msc 
Right click on SQL Server Services and go to Properties. 
The account information is available under Log On Tab

And also service account information stored in Windows Registry, using xp_instance_regread extended stored procedure we can read windows registry.  Registry entries are like below 
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLServerAgent
Using below script also we can fetch the information   
DECLARE       @DBEngineLogin       VARCHAR(100)
DECLARE       @AgentLogin          VARCHAR(100)

EXECUTE       master.dbo.xp_instance_regread
              @rootkey      = N'KEY_LOCAL_MACHINE',
              @key          = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',
              @value_name   = N'ObjectName',
              @value        = @DBEngineLogin OUTPUT

EXECUTE       master.dbo.xp_instance_regread
              @rootkey      = N'HKEY_LOCAL_MACHINE',
              @key          = N'SYSTEM\CurrentControlSet\Services\SQLServerAgent',
              @value_name   = N'ObjectName',
              @value        = @AgentLogin OUTPUT

SELECT        [DBEngineLogin] = @DBEngineLogin, [AgentLogin] = @AgentLogin
GO

Output 
in above scenario both Agent and SQL Services are running as Local System 
Using DMVS also we can fetch the information   

SELECT
      servicename
      , service_account
      , startup_type_desc
      , status_desc
      , is_clustered
FROM   sys.dm_server_services


Output :

in above scenario SQL Services is running as NT Service and Agent is running with Domain Account. 


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 Ca...