Pages

Showing posts with label SQL Server 2008. Show all posts
Showing posts with label SQL Server 2008. Show all posts

Wednesday, September 21, 2016

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##';

Friday, October 24, 2014

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 Standard
User: sa
Password:   will be the same as your administrator or root user password at the time the VDS was provisioned.

Monday, March 10, 2014

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

Monday, March 11, 2013

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.

 

Monday, February 11, 2013

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

Tuesday, October 30, 2012

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

Wednesday, September 12, 2012

Friday, June 29, 2012

Wednesday, August 31, 2011

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

Saturday, April 30, 2011

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