SA PASSWORD reset if you do not have any credential

When you are using MS SQL Server in mixed mode, it is very important that you know your SA password.

There can be different reasons you lost the password

·         Person who installed the SQL Server knows the password but has left the building.

·         You did not write down the password in your password file

·         Password file is lost

Steps to recover the SA password:

1.    Start SQL Server Configuration Manager

    2.    Stop the SQL services

    3.   Edit the properties of the SQL Service

    4.   Change the startup parameters of the SQL service by adding a –m; in front of the existing parameters.

    5. Start the SQL services. These are now running in Single User Mode

    6. Start CMD on tthe SQL server

    7. Start the SQLCMD command. Now you will see following screen.

    8.Now we create a new user. Enter following commands

            CREATE LOGIN crmuser WITH PASSWORD = 'hcl@123'

   CREATE LOGIN [Login_name] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

 9.  Now we grant the user a SYSADMIN roles using the same SQLCMD window.

         

                        sp_addsrvrolemember ‘crmuser’, ‘sysadmin’

                        go

          EXEC master..sp_addsrvrolemember @loginame = 'login_name',

          @rolename = 'sysadmin'

10.  Stop the SQL service again

11.  Change the SQL service properties back to the default settings.

 12.  Start the SQL server and go via the security panel to the properties and change the password of the SA   
       account.

   ‘sa’ because the account is currently locked out:

  Fix/Solution/Workaround:

1.       Disable the policy on your system or on your domain level. However, this may not be the most 
       appropriate option as it will adversely affect your security protection level.

2.        If this is a one-time issue, enable “sa” login WITH changing password of “sa” login.

        ALTER LOGIN  sa  WITH PASSWORD = 'yourpass' UNLOCK ;

        GO

3.       If this is a one-time issue, enable “sa” login WITHOUT changing password of “sa” login.

       ALTER LOGIN sa WITH CHECK_POLICY = OFF;

WMI Error

SQL Server Configuration Manager – Cannot connect to WMI provider – Provider load failure [0x80041013]

As soon as he was trying to launch SQL Server Configuration Manager, he was seeing below error.

This problem occurs when we have installed both 32 bit version and 64 bit version of SQL Server on same Windows Machine and when we uninstall an instance of SQL Server,  WMI provider is also removed.The 32-bit instance and the 64-bit instance of SQL Server share the same WMI configuration file. This file is located in the %programfiles(x86)% folder.

To Resolve this issue, we need to locate the file “Sqlmgmproviderxpsp2up.mof” which is present at the

"%programfiles(x86)%\Microsoft SQL Server\number\Shared folder"

The value of number depends on the version of SQL Server

Microsoft SQL Server 2012	110
Microsoft SQL Server 2008 R2	100
Microsoft SQL Server 2008	100

hen, from Command Prompt we need to run below command.

mofcomp “%programfiles(x86)%\Microsoft SQLServer\number\Shared\sqlmgmproviderxpsp2up.mof”

DBCC CHECKDB

Checks the logical and physical integrity of all the objects in the specified database by performing the following operations:

·         Runs DBCC CHECKALLOC on the database.

·         Runs DBCC CHECKTABLE on every table and view in the database.

·         Runs DBCC CHECKCATALOG on the database.

·         Validates the contents of every indexed view in the database.

·         Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.

·         Validates the Service Broker data in the database.

DBCC CHECKDB is supported on databases that contain memory-optimized tables but validation only occurs on disk-based tables. However, as part of database backup and recovery, a CHECKSUM validation is done for files in memory-optimized filegroups.

Since DBCC repair options are not available for memory-optimized tables, you must back up your databases regularly and test the backups. If data integrity issues occur in a memory-optimized table, you must restore from the last known good backup.

DBCC CHECKDB 

[

    [ ( database_name | database_id | 0

        [ , NOINDEX 

        | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]

    ) ]

    [ WITH 

        {

            [ ALL_ERRORMSGS ]

            [ , EXTENDED_LOGICAL_CHECKS ] 

            [ , NO_INFOMSGS ]

            [ , TABLOCK ]

            [ , ESTIMATEONLY ]

            [ , { PHYSICAL_ONLY | DATA_PURITY } ]

        }

    ]

]

This means that the DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKCATALOG commands do not have to be run separately from DBCC CHECKDB.

-- Check the current database.

DBCC CHECKDB;

GO

-- Check the AdventureWorks2012 database without nonclustered indexes.

DBCC CHECKDB (AdventureWorks2012, NOINDEX);

GO

DBCC CHECKALLOC

Checks the consistency of disk space allocation structures for a specified database.

DBCC CHECKALLOC 

[

    ( database_name | database_id | 0 

      [ , NOINDEX 

      | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]

    )

    [ WITH 

        { 

          [ ALL_ERRORMSGS ]

          [ , NO_INFOMSGS ] 

          [ , TABLOCK ] 

          [ , ESTIMATEONLY ] 

        }

    ]

]

-- Check the current database.

DBCC CHECKCATALOG;

GO

-- Check the AdventureWorks2012 database.

DBCC CHECKCATALOG (AdventureWorks2012);

GO

DBCC CHECKCATALOG

Checks for catalog consistency within the specified database. The database must be online.

DBCC CHECKCATALOG 

[ 

    ( 

    database_name | database_id | 0

    )

]

    [ WITH NO_INFOMSGS ] 

-- Check the current database.

DBCC CHECKCATALOG;

GO

-- Check the AdventureWorks2012 database.

DBCC CHECKCATALOG (AdventureWorks2012);

GO

DBCC CHECKTABLE

Checks the integrity of all the pages and structures that make up the table or indexed view.

DBCC CHECKTABLE 

(

    table_name | view_name

    [ , { NOINDEX | index_id }

     |, { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } 

    ] 

)

    [ WITH 

        { ALL_ERRORMSGS ]

          [ , EXTENDED_LOGICAL_CHECKS ] 

          [ , NO_INFOMSGS ]

          [ , TABLOCK ] 

          [ , ESTIMATEONLY ] 

          [ , { PHYSICAL_ONLY | DATA_PURITY } ] 

        }

    ]

To perform DBCC CHECKTABLE on every table in the database, use DBCC CHECKDB.

For the specified table, DBCC CHECKTABLE checks for the following:

• Index, in-row, LOB, and row-overflow data pages are correctly linked.
• Indexes are in their correct sort order.
• Pointers are consistent.
• The data on each page is reasonable, included computed columns.
• Page offsets are reasonable.
• Every row in the base table has a matching row in each nonclustered index, and vice-versa.
• Every row in a partitioned table or index is in the correct partition.
• Link-level consistency between the file system and table when storing varbinary(max) data in the file system using FILESTREAM.

Result set:

DBCC results for 'HumanResources.Employee'.

There are 288 rows in 13 pages for object 'Employee'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

https://msdn.microsoft.com/en-us/library/ms176064.aspx