Interactive Services Dialog Detection window for SQL Agent

Today I got the below error on my client machine: 

"Interactive Services Dialog Detection"

The solution of above problem is desable the interactive service dialog detection window. Follow below steps:

• Go to Start - Run and type services.msc to Starts, stops, and configures Windows services.
• Select SQL Server Agent--> Right click on properties
• Select the logon tab
• Uncheck the “Allow Service to interact with Desktop”
• Restart the SQL Server Agent

Cannot connect to WMI provider. You do not have permission or the server is unreachable.

Today when I was trying to open SQL Server 2012 configuration manager, I got below error: 

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. 
Invalid class [0x80041010] 

I follow below steps to solve above error:

1. First I search for the file "sqlmgmproviderxpsp2up.mof"  (generally it is located under shared folder under version folder). and open command prompt and run below command:
mofcomp C:\Program Files (x86)\Microsoft SQL Server\110\Shared\sqlmgmproviderxpsp2up.mof
Output
Microsoft (R) MOF Compiler Version 6.1.7600.16385
Copyright (c) Microsoft Corp. 1997-2006. All rights reserved.
Parsing MOF file: C:\Program Files (x86)\Microsoft SQL Server\110\Shared\sqlmgmp
roviderxpsp2up.mof
MOF file has been successfully parsed
Storing data in the repository...
Done!
2. After it is stored in the repository, 
3. Now open SQL Configuration manager, it will work.

Retrieve SQL Server Service Account

This script will pull the name of the SQL Server Service account out of the registry using an undocumented stored procedure. 


-- Declare a variable to hold the value 
DECLARE @serviceaccount varchar(100)

-- Retrieve the Service account from registry 
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
N'ObjectName',
@ServiceAccount OUTPUT,
N'no_output'

--Display the Service Account 
SELECT @Serviceaccount

Row count for all the tables in all the database

Use below query to get the row count for all tables in all the databases:


SET NOCOUNT ON
DECLARE @strQuery VARCHAR(4000)
DECLARE @strTable TABLE (DBName VARCHAR(200), TableName VARCHAR(300), COUNT INT)
SET @strQuery='SELECT  ''?'',sysobjects.Name, sysindexes.Rows
FROM   ?..sysobjects INNER JOIN ?..sysindexes ON sysobjects.id = sysindexes.id
WHERE  type = ''U''  AND sysindexes.IndId < 2 order by sysobjects.Name'
INSERT @strTable
EXEC sp_msforeachdb @strQuery
SELECT * FROM @strTable WHERE DBName <> 'tempdb' ORDER BY DBName

CHECK_EXPIRATION option cannot be used when CHECK_POLICY is OFF

Sometime you may get the below error while executing some script:

Msg 15122, Level 16, State 1, Line 1The CHECK_EXPIRATION option cannot be used when CHECK_POLICY is OFF.

To solve this error, Turn On CHECK_POLICY in the script or remove CHECK_EXPIRATION from the script.

CREATE LOGIN [username] WITH PASSWORD=N'pa@Ssword' , CHECK_EXPIRATION=ON, CHECK_POLICY=ON