Get SQL Server Custom Login Password expiration Details

 The below code will execute in the SQL server then you will get the details


--Show all logins password expiration date and creation date

SELECT name AS SQLLoginName,
   DATEADD(DAY, CAST(LOGINPROPERTY(name, 'DaysUntilExpiration') AS int), GETDATE())
AS ExpirationDate,
   create_date
   FROM sys.server_principals
   WHERE type = 'S'


-- Show all logins where the password was changed within the last day

SELECT name, LOGINPROPERTY([name], 'PasswordLastSetTime') AS 'PasswordChanged'
FROM sys.sql_logins
WHERE LOGINPROPERTY([name], 'PasswordLastSetTime') > DATEADD(dd, -1, GETDATE());


-- Show all logins where the password is over 60 days old

SELECT name, LOGINPROPERTY([name], 'PasswordLastSetTime') AS 'PasswordChanged'
FROM sys.sql_logins
WHERE LOGINPROPERTY([name], 'PasswordLastSetTime') < DATEADD(dd, -60, GETDATE())
  AND NOT (LEFT([name], 2) = '##' AND RIGHT([name], 2) = '##');


--Change Password via code

ALTER LOGIN TestDBs
WITH PASSWORD = 'Asdeft##';

What is the default password for the sa login for SQL server?

when you installing SQL server you already give password for sa on the Mixed mode, If you are not remember then pls refer in the following points


The default password for the sa login will depend on the version of SQL Server installed and if you are using SQL Express or a pruchased version of SQL Server that we have installed for you.   For SQL Server Express users, once you are logged in, please change your password.

SQL Server 2008/R2 Express
User:  sa
Password:   [blank password - leave field empty to connect]

SQL Server 2012 Express
User:  sa
Password: Password123


SQL Server 2008/R2/2012 Web or StandardUser: sa
Password:   will be the same as your administrator or root user password at the time the VDS was provisioned.

Command to map an orphaned user for SQL Server Login id and Database user id

After restoring the SQL Server Database from one server to another server, SQL Server Login id and Database user id does match, so it look like a orphaned user, to fix the issue then follow the commands


-Script to view difference in SID

USE MASTER
GO
SELECT name as SQLServerLogIn,SID as SQLServerLogInSID FROM sys.syslogins
WHERE [name] = 'TestUser3'
GO

USE AdventureWorks
GO
SELECT name DataBaseUserID,SID as DatabaseUserSID FROM sysusers
WHERE [name] = 'TestUser3'
GO

--Command to generate list of orphaned users

USE adventureWorks
GO

sp_change_users_login @Action='Report'
GO


--Command to map an orphaned user

USE AdventureWorks
GO

sp_change_users_login @Action='update_one',
@UserNamePattern='TestUser1',
@LoginName='TestUser1'
GO

How to Import the Excel Data into the MS SQL Database table

Create a Dummy table name is Dummy_Tyring and create column name as per Excel Sheet.
The existing table name is Properties

steps:
1.Just copy all data from Excel File and paste into the Dummy_Tyring table

2.Execute the below update Query
update [Properties]
set [Properties].Tiring = Dummy_Tyring.Tiring
FROM [Properties]
INNER JOIN Dummy_Tyring ON [Properties].PropertyPIN = Dummy_Tyring.PIN

3.Now all the dummy data information has moved to Existing Database table.

 

Update same table column using Join

UPDATE t2
SET t2.FinalValue = t3.FinalValue ,t2.Updated='true'--t3.FinalValue
from #tbl_3 t2 INNER JOIN #tbl_3 t3 ON t3.FTValue=t2.FTValue
WHERE t2.Updated='false'

SQL Query Tasks

Random Number Generation
Convert Datatype
Replace String
Numeric value Retrieval

1.Generate random number in the SQL Table
ROW_NUMBER() OVER (ORDER BY EmployeeName) AS Row
------------------
2.SQL Column convert from varchar value into Integer value

CAST([Value] AS INT) AS PeerValue
--------------------------

3.SQL Column convert from int value into varchar value
CAST(Prset AS varchar(10)) AS FinalValue
------------------
4. Replace SQL Column contains "Comma" character to Null Values
CAST(Replace([Value],',','') AS FLOAT) AS PrsetValue
5. SQL Column retrieve only numeric Value, It will retrict Alphabets,special Characters
[Value] not like '%[^0-9]%'

Table Column value retrieved using Multiple Joins

select p.Hotel as [Pro],l1.LookupText AS [Met1],l2.LookupText AS [Met2],l3.LookupText AS Met3,[Value],[Year] from [Pro] p INNER JOIN Procs_Seg_EXL E on
p.PropID=E.PropID
INNER JOIN [LKUP] l1 on l1.lupid=E.Me1
INNER JOIN [LKUP] l2 on l2.lupid=E.Me2
INNER JOIN [LKUP] l3 on l3.lupid=E.Me3

Create Temp Tables in SQL Server

Create PROCEDURE [dbo].[GetTempData]
(
   @RegionID varchar(max) = 0
)
AS
CREATE TABLE #tbl_2
(
PropertyID VARCHAR(250),
FTValue int,
FinalValue VARCHAR(250)
)
CREATE TABLE #tbl_3
(
PropertyID VARCHAR(250),
FTValue int,
FinalValue VARCHAR(250)
)

Insert Into #tbl_2 (PropertyID,FTValue,FinalValue) Values('141',5000,'1/4')
Insert Into #tbl_2 (PropertyID,FTValue,FinalValue) Values('142',4000,'2/4')
Insert Into #tbl_2 (PropertyID,FTValue,FinalValue) Values('143',4000,'3/4')
Insert Into #tbl_2 (PropertyID,FTValue,FinalValue) Values('144',3000,'4/4')

INSERT #tbl_3 SELECT PropertyID,FTValue,FinalValue from #tbl_2

select * from #tbl_3
DROP TABLE #tbl_2
DROP TABLE #tbl_3
END

Move Sharepoint Content Database from SQl server 2005 to SQl server 2008 R2

Attach and Detach SQL server database 2005 to 2008 R2

It is going on

Copy from One database table data to anoother database table data MS SQL Query

insert into StarDB.dbo.Associate1 select * from StarDB_Production.dbo.Associate1

Query for SQL 2008

It is Process

MS SQL Server Connection String

SQl Server Connection String

Shrink SQL Server Database

Method 1

ALTER DATABASE DatabaseName SET RECOVERY SIMPLE
use DatabaseName
GO
CHECKPOINT
GO
DBCC SHRINKFILE(transactionloglogicalfilename,TRUNCATEONLY)
GO
ALTER DATABASE DatabaseName SET RECOVERY FULL

Method 2

USE DatabaseName
GO
ABCC SHRINKFILE(, 1)
BACKUP LOG WITH TRUNCATE_ONLY
ABCC SHRINKFILE(, 1)
GO

PIVOT TABLE and SQL Query

Basic Example for PIVOT:
SELECT gid,
[1] AS cname1,
[2] AS Cname2,
[3] AS Cname3,
[4] AS Cname4,
[5] as Cname5
FROM
(SELECT gid,gpid,gpname,Name FROM test) s
PIVOT
(
MAX(Name)
FOR gname IN ([1],[2],[3],[4],[5])
) p
ORDER BY [gid]
GO