Click here to Skip to main content
15,741,011 members
Articles / Programming Languages / SQL
Technical Blog
Posted 11 Jul 2016

Tagged as


12 bookmarked

Some SQL Tips

Rate me:
Please Sign up or sign in to vote.
4.53/5 (14 votes)
11 Jul 2016CPOL
Some SQL Server Query tricks

1. I am back after a very long break. So, I somehow forgot SQL Server ‘SA’ Password, and this is how I recovered that.

  • Change the startup parameters by adding add -m;
  • Start SQL Server
  • Open command prompt and type SQLCMD

Method 1: Create new user and add to systemadmin role

CREATE LOGIN recovery1 WITH PASSWORD = 'abc123%';
sp_addsrvrolemember 'recovery1', 'sysadmin';

Now login using created user and change the ‘Sa’ password.

Method 2: Change Password

EXEC sp_password NULL, abc123!@#$', 'sa';

Method 3: Give system admin roles to the Windows user

EXEC sp_addsrvrolemember 'Softcat\tharaka_r', 'sysadmin';

2. I could not remember who wrote this query, but I am sure it was not me. :p So, I just simplified that.

DECLARE @RunDate AS DATETIME = getdate()
SELECT CONCAT (DATEPART(yyyy,DATEADD(dd,30,@RunDate)),'-', _
DATEPART(mm,DATEADD(dd,30,@RunDate)),'-', _
DATEPART(dd,DATEADD(dd,30,@RunDate))) AS DateNow , 30 AS Value;

The simplified version is as given below:

DECLARE @RunDate AS DATETIME = getdate();
SELECT CONVERT(VARCHAR(10),DATEADD (dd,30, @RunDate),120) AS DateNow , 30 AS Value;

Filed under: CodeProject, SQL
Tagged: Database, SQL, SQL Server

This article was originally posted at


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Technical Lead Eyepax IT Consulting (Pvt) Ltd.
Sri Lanka Sri Lanka
Having more than 9 year hands-on industry experience in software development
Responsible for designing, implementing and managing complex software systems with stringent up-time requirement.

Visit my blog

Comments and Discussions

QuestionBad practice, if you ask me... first of.. Pin
Paw Jershauge14-Jul-16 21:50
Paw Jershauge14-Jul-16 21:50 
Questionyeah Pin
Robert Yank13-Jul-16 21:51
Robert Yank13-Jul-16 21:51 
Wow! thanks for that! Very cool!
GeneralMy vote of 3 Pin
Vipin_Arora12-Jul-16 21:19
Vipin_Arora12-Jul-16 21:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.