How To Rename or Alter a Login name and User name in SQL Server ?

We have seen so many scenario when we need to Alter login to change a password but in very rare cases we Alter a Login means rename a Login.

Today i got one request related to create a SQL Account like VPUSER and i created the user with same name and tell that its done.

After a while he again requested can you please delete this user and create new user with the name of [VPAPPUSER] .When i asked the exact requirement why he wants to delete old user and create new one so he explained me the SQL Login account should be according of naming convention .

Then i thought i will not delete the login i will alter the login if i delete the login then i have to do lot of things after creation login like need to give all permission again.

So i Alter the Login and User as well with following command and i saved all the time .


ALTER LOGIN VPUSER WITH NAME = [VPAPPUSER];

If you will not change the User name it will work as it is but user name will be different. It will be better if naming convention should be same

ALTER USER VPUSER WITH NAME = [VPAPPUSER];

Rebuild System Databases in MSSQL Server 2012

Finally I got the chance to work on rebuild system database in SQL Server 2012.

To rebuild the system database is the last preference from my side.

Why we need to rebuild the System database and when?

Situation:-

When your Master database corrupt and you are not able to start SQL Server .

Here is two type of corruption for Master database:-

1)      Partial corrupt

Partial corrupt means: - There are few pages corrupted from Master database not full database. So, if you are able to connect Instance by  single user mode  means its partial corrupt and you can restore the last backup copy from backups and it will start normally.

2)      Fully Corrupt

Fully corrupt: Fully corrupt means you are not able to restart SQL Server in single user mode and there is no other way to rebuild Master databases.

To rebuild a System databases is a complicated process so before go to rebuild Master database we can try other method through we can start our SQL Server Instance.

So solution is if you have any other instance running or any other Server with the same edition and build number we can stop that Server and copy the Master database .LDF and MDF files and Paste these files against corrupted Server and restart the server .98 % it will we up and work but may be you will lose your login and user but that also we can recover if we have last full backup of Master database so just restore that database with single user mode and it will work you can recover all the data now.

If there is no other Instance with the same edition or you are not able to start SQL Server with other SQL Server Master .Ldf and .mdf file ,so last way is rebuild the system databases.

Precaution before rebuild the System Databases:-

1)      Take backup for all the user and system database backup by copy and paste .mdf and ldf file data file and keep it some safe place.

Why these backup  are  mandatory  before rebuild the system database?

Whenever we rebuild the system database it will recreate all the system database from scratch and if you didn’t  take backup of other database like MSDB, model and user database ,so we can lose the important information and data like all jobs related information from MSDB database and some property feature if we setup in model database for every new creation of DB and during rebuild the system database sql server remove all the user databases so make sure we need to take all backup before rebuild system databases.

So I hope now you will understand the importance of keep backup before rebuild system databases.

Let start with Re-build process:-

Step 1:- Go To >>Programe Files>>Microsoft SQL Server>>110>>Setup Bootstrap>>SQlServer2012>>Setup.exe

Step 2:-

Open CMD by RUN as Administrator

Type below command

C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012

Step 3:-

setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=SQlServer2012 /SQLSYSADMINACCOUNTS=Ashish-PC\Ashish /SAPWD=123456

Done!

Page Level Restore In SQL Server 2012

How to do Page level restore in SQL Server 2012.

We found that our DBCC checkDB job got failed due to some inconsistency error and we dig more into this error and finally found that there is one page has been corrupted.

The Restore Pages feature let us restore damaged pages without restoring the entire database. These pages are marked as suspect.

Restore pages is only possible in the full and bulk‑logged recovery model. We can only restore database pages.

How we come to know page is really corrupted:-

Step 1:

 we checked and verified error log in SQL Server and found below error:-

Step 2:-

Run below command and found corrupted page information as per above error for database id 20 

Step 3:-

We found the database name with database id 20 with below command.

Step 4 :-

As of now we have found database name now we need to find that object from where page has corrupted.

For below command you can achieve this.

Step:5

Once you got the object ID you can find the table name by below command

Step :6

Now we try to run more command DBCC CHECKTable but we got similar error:-

Step :7

Finally we decide to look into corrupted page id with below command:-

Step 8:

Finally we decided to Page restore by SSMS 2012 but for Page restore make sure your database on full recovery mode.

In order to restore the page, I select the database, I right‑click on it (from the Object Explorer in

the SSMS) then select Tasks‑>Restore‑>Page .

You can see below corrupted Page id and file id automatically

  

 Click on the ok Button

STEP :9

After restoration we ran again check DB and it was success

Same as DBCC Checktable

How to Revoke backup permission from user who have DB_Owner permission

Revoke backup Database from UserName
Revoke backup log from UserName

Provide Permission again:-

Grant backup database to UserName