Microsoft SQL Server

Copying And Moving SQL Server Logins

2011-09-01T02:52:00.000-07:00

----------- copying-and-moving-sql-server-logins -------------------

--- STEP : 1

EXEC sp_helplogins

---------------------------------------------------
--- STEP : 2
SELECT
p.name [LoginName]
u.name [UserName]
'User' [Type]
FROM
sys.sysusers u
INNER JOIN sys.server_principals p
ON u.sid = p.sid
UNION SELECT
1.name,
u2.name,
'MemberOf'
FROM
sys.database_role_members m
,sys.database_principals U1
,sys.database_principals u2
,sys.server_principals 1
WHERE
u1.sid = 1.sid
and m.member_principal_id = u1.principal_id
and m.role_principal_id = u2.principal_id

---------------------------------------------------
--- STEP : 3
--- http://support.microsoft.com/kb/918992

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length) BEGIN DECLARE @tempint int DECLARE @firstint int DECLARE @secondint int SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SELECT @firstint = FLOOR(@tempint/16) SELECT @secondint = @tempint - (@firstint*16) SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1) SELECT @i = @i + 1 END SELECT @hexvalue = @charvalue GO IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin GO CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS DECLARE @name sysname DECLARE @type varchar (1) DECLARE @hasaccess int DECLARE @denylogin int DECLARE @is_disabled int DECLARE @PWD_varbinary varbinary (256) DECLARE @PWD_string varchar (514) DECLARE @SID_varbinary varbinary (85) DECLARE @SID_string varchar (514) DECLARE @tmpstr varchar (1024) DECLARE @is_policy_checked varchar (3) DECLARE @is_expiration_checked varchar (3) DECLARE @defaultdb sysname IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR

SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
---------------------------------------------------
--- STEP : 4
--- After STEP 3 done execute this script
EXEC sp_help_revlogin
---------------------------------------------------
--- STEP : 5
--- Copy the Script from Message box
--- Open the new destination server
--- Open the Copyed DB
--- New Query Window
--- Paste the Copyed script
--- Remove unwanted Script/Lines

---------------------------------------------------
--- STEP : 6
--- Type Use Master - exec only this
--- Change if need anything in the script

---------------------------------------------------
--- STEP : 7
--- Open Server Manager
--- Go to Configuration
--- Go to Local Users and Group
--- Go to Users
--- RC new user
--- Type User name , Full name , Password , Confirm password
--- Set user cannot change password & Password never expires
--- Click Create
---------------------------------------------------
--- STEP : 8
--- Then run the login Script

---------------------------------------------------
--- STEP : 9 To check
Exec sp_change_users_login 'report'

---------------------------------------------------
--- STEP : 10 To Check

SELECT
p.name [LoginName]
u.name [UserName]
'User' [Type]
FROM
sys.sysusers u
INNER JOIN sys.server_principals p
ON u.sid = p.sid
UNION SELECT
1.name,
u2.name,
'MemberOf'
FROM
sys.database_role_members m
,sys.database_principals U1
,sys.database_principals u2
,sys.server_principals 1
WHERE
u1.sid = 1.sid
and m.member_principal_id = u1.principal_id
and m.role_principal_id = u2.principal_id

---------------------------------------------------
--- STEP : 11
EXEC sp_change_users_login
@Action = 'Update_One',
@UserNamePattern = 'OrphanedUserHere',
@LoginName = 'LoginNameToMapToHere',
@Password = NULL
Go

---------------------------------------------------
--- STEP : 12
EXEC sp_change_users_login
@Action = 'Auto_FIx',
@UserNamePattern = 'OrphanedUserHere',
@LoginName = NULL, -- Create a new login based on user name
@Password = 'passwordhere!'
Go

BACKUP Details

2011-09-01T02:44:00.000-07:00

BACKUP Details

---Full & Differential Backup Details
DECLARE @dbname sysname
SET @dbname = NULL --set this to be whatever dbname you want
SELECT bup.user_name AS [User],
bup.database_name AS [Database],
bup.server_name AS [Server],
bup.backup_start_date AS [Backup Started],
bup.backup_finish_date AS [Backup Finished]
,CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/3600 AS varchar) + ' Hours, '
+ CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/60 AS varchar)+ ' Minutes, '
+ CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))%60 AS varchar)+ ' Seconds'
AS [Total Time]
FROM msdb.dbo.backupset bup
WHERE bup.backup_set_id IN
(SELECT MAX(backup_set_id) FROM msdb.dbo.backupset
WHERE database_name = ISNULL(@dbname, database_name) --if no dbname, then return all
AND type = 'D' --only interested in the time of last full backup
-- AND type = 'I' -- --only interested in the time of last Differential backup
and bup.backup_start_date >= '2011-03-11 18:00:00.000'
GROUP BY database_name)
/* COMMENT THE NEXT LINE IF YOU WANT ALL BACKUP HISTORY */
AND bup.database_name IN (SELECT name FROM master.dbo.sysdatabases)
--ORDER BY bup.backup_start_date
ORDER BY bup.backup_finish_date
--ORDER BY bup.database_name

--- Backup histroy
DECLARE @dbname sysname
SET @dbname = NULL --set this to be whatever dbname you want
SELECT bup.user_name AS [User],
bup.database_name AS [Database],
bup.server_name AS [Server],
bup.name AS [BackupType],
bup.backup_start_date AS [Backup Started],
bup.backup_finish_date AS [Backup Finished]
,CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/3600 AS varchar) + ' hours, '
+ CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/60 AS varchar)+ ' minutes, '
+ CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))%60 AS varchar)+ ' seconds'
AS [Total Time]
FROM msdb.dbo.backupset bup
/* COMMENT THE NEXT LINE IF YOU WANT ALL BACKUP HISTORY */
WHERE bup.database_name IN (SELECT name FROM master.dbo.sysdatabases)
ORDER BY bup.database_name

--- When Last backup Happend
SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name

--- Days Since LastBackup
SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(A.backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as LastBackupDate
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A
ON A.database_name = B.name AND A.type = 'D' GROUP BY B.Name ORDER BY B.name

select top 1 * from msdb.dbo.sysjobhistory
select top 1 * from msdb.dbo.sysjobs

Collation

2011-09-01T02:37:00.000-07:00

Collation

-- Script that returns all available collations
select * from fn_helpcollations()
-- Script that returns all your databases' collations
select name, collation_name from sys.databases
-- Test_DBA SQL_Latin1_General_CP1_CI_AS
-- Changing Collation setting at Database Level
use master
ALTER database Test_DBA set single_user with rollback immediate
ALTER DATABASE Test_DBA COLLATE SQL_Latin1_General_CP1_CI_AS ;
ALTER database Test_DBA set multi_user

/* Script to compare all Collation settings
Returns 2 results sets
1) Databases where collation is different from server setting
2) Columns where collation is different from database setting
*/
IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS
WHERE ID = OBJECT_ID('tempdb.dbo.#CollationComparison'))
DROP TABLE #CollationComparison
CREATE TABLE #CollationComparison
(Database_Name SYSNAME
,Table_Schema SYSNAME
,Table_Name SYSNAME
,Column_Name SYSNAME
,Server_Collation SYSNAME
,Database_Collation SYSNAME
,Column_Collation SYSNAME)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @dbname NVARCHAR(200)
DECLARE dbcursor CURSOR FOR
select name from sys.databases where state_desc='Online'
OPEN dbcursor
FETCH NEXT FROM dbcursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
print @dbname
SET @SQL = 'INSERT INTO #CollationComparison
(Database_Name
,Table_Schema
,Table_Name
,Column_Name
,Server_Collation
,Database_Collation
,Column_Collation)
SELECT
C.TABLE_CATALOG AS DATABASE_NAME
,C.TABLE_SCHEMA
,C.TABLE_NAME
,C.COLUMN_NAME
,CONVERT(VARCHAR,SERVERPROPERTY(''COLLATION'')) AS SERVER_COLLATION
,CONVERT(SYSNAME,DATABASEPROPERTYEX(D.NAME,''COLLATION'')) AS DATABASE_COLLATION
,C.COLLATION_NAME AS COLUMN_COLLATION
FROM [' + @dbname + '].INFORMATION_SCHEMA.COLUMNS C
INNER JOIN SYS.DATABASES D
ON DB_ID(C.TABLE_CATALOG) = DB_ID(D.NAME)
WHERE DATA_TYPE IN (''VARCHAR'' ,''CHAR'',''NVARCHAR'',''NCHAR'',''TEXT'',''NTEXT'')
'
exec sp_executesql @SQL
print @sql
FETCH NEXT FROM dbcursor INTO @dbname
END
CLOSE dbcursor
DEALLOCATE dbcursor
SELECT DISTINCT Server_Collation,Database_Collation,Database_Name
FROM #CollationComparison
WHERE Server_Collation <> Database_Collation
SELECT DISTINCT top 1 * FROM #CollationComparison WHERE Column_Collation <> Database_Collation

--SELECT name,state_desc as DatabaseStatus_sysDatabase
--FROM sys.databases where state_desc='Online'
--
--SELECT *
--FROM sys.databases where state_desc='Online'

USE master;
GO
CREATE DATABASE testdb
COLLATE SQL_Latin1_General_CP1_CI_AS ;
GO
ALTER DATABASE testDB
COLLATE French_CI_AI ;
GO

Comma Seperater

2011-09-01T02:34:00.000-07:00

Comma Seperater

--- Create Function
CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end

--- drop function dbo.Split

-- Test 1
select top 10 * from dbo.split('Chennai,Bangalore,Mumbai',',')
-- Test 2
--select * from [dbo].[UDF_SPLIT]('Chennai,Bangalore,Mumbai',',')

---Insert into Temp Table
create table #temptable (id int identity(1,1),col1 varchar(255))
insert into #temptable
select * from dbo.split('Chennai,Bangalore,Mumbai',',')
select * from #temptable

---Another Method
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION SplitForDelimiter
(
@delimiter VARCHAR(10),
@input VARCHAR(1000)
)
RETURNS @tempTable TABLE(
data VARCHAR(100)
)
BEGIN
DECLARE @tempstr VARCHAR(1000)
SET @tempstr = @input
WHILE(charindex(@delimiter,@tempstr,0) > 0)
BEGIN
DECLARE @t VARCHAR(100)
SET @t = Substring(@tempstr,0,(charindex(@delimiter,@tempstr,0)))
INSERT into @tempTable (data) VALUES (@t)
SET @tempstr = Substring(@tempstr,charindex(@delimiter,@tempstr,0)+1,Len(@tempstr))
if(charindex(@delimiter,@tempstr,0) <=0)
BEGIN
INSERT into @tempTable (data) VALUES (@tempstr)
END
END
RETURN
END

All about space Details

2011-09-01T02:29:00.000-07:00

--- To Get Drive Space
--use master
--go
--CREATE PROCEDURE sp_diskspace
--AS
--SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint ; SET @MB = 1048576
CREATE TABLE #drives (drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL)
INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives
SET TotalSize=@TotalSize/@MB
WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
SELECT
(SELECT SERVERPROPERTY ('MachineName')) As ServerName,
drive,
TotalSize as 'Total(MB)',
(TotalSize - FreeSpace) As 'Utilized(MB)',
--Utilized= TotalSize - FreeSpace ,
FreeSpace as 'Free(MB)',
FreeSpace/1024 as 'Free(GB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)',
CONVERT(VARCHAR(12),getdate(),113) As 'As ON'
FROM #drives
ORDER BY drive
DROP TABLE #drives
RETURN
--go

---To Get MDF Size
use master;
go
select
(SELECT SERVERPROPERTY ('MachineName')) As ServerName,
--db.[dbid] as 'DB ID'
db.[name] as 'Database Name'
,af.[name] as 'Logical Name'
,af.[filename] as 'Physical Name'
--,af.[size] as 'File Size (in 8-kilobyte (KB) pages)'
,(((CAST(af.[size] as DECIMAL(18,4)) * 8192) /1024) /1024) as 'File Size (MB)'
--,((((CAST(af.[size] as DECIMAL(18,4)) * 8192) /1024) /1024) /1024) as 'File Size (GB)'
from sys.sysdatabases db
inner join sys.sysaltfiles af
on db.dbid = af.dbid
where [fileid] in (1)
order by db.[name]

---To Get NDF Size
use master;
go
select
--db.[dbid] as 'DB ID'
db.[name] as 'Database Name'
,af.[name] as 'Logical Name'
,af.[filename] as 'Physical Name'
--,af.[size] as 'File Size (in 8-kilobyte (KB) pages)'
,(((CAST(af.[size] as DECIMAL(18,4)) * 8192) /1024) /1024) as 'File Size (MB)'
--,((((CAST(af.[size] as DECIMAL(18,4)) * 8192) /1024) /1024) /1024) as 'File Size (GB)'
from sys.sysdatabases db
inner join sys.sysaltfiles af
on db.dbid = af.dbid
where [fileid] not in (1,2)
order by db.[name]

---To Get LDF Size
use master;
go
select
--db.[dbid] as 'DB ID',
db.[name] as 'Database Name'
,af.[name] as 'Logical Name'
,af.[filename] as 'Physical Name'
--,af.[size] as 'File Size (in 8-kilobyte (KB) pages)'
,(((CAST(af.[size] as DECIMAL(18,4)) * 8192) /1024) /1024) as 'File Size (MB)'
--,((((CAST(af.[size] as DECIMAL(18,4)) * 8192) /1024) /1024) /1024) as 'File Size (GB)'
from sys.sysdatabases db
inner join sys.sysaltfiles af
on db.dbid = af.dbid
where [fileid] in (2)
order by db.[name]

SQL Server Service Startup Time

2011-09-01T01:57:00.000-07:00

--- To Check when Sql service is started

use master

SELECT login_time as ServiceStartUpTIme FROM sysprocesses WHERE spid = 1

---OutPut

--- To check how long it is running
SET NOCOUNT ON
DECLARE @crdate
DATETIME, @hr VARCHAR(50), @min VARCHAR(5)
SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'
SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60
IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
ELSE
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hr+' hours & '+@min+' minutes'
IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
PRINT 'SQL Server is running but SQL Server Agent <> running'
END
ELSE
BEGIN
PRINT 'SQL Server and SQL Server Agent both are running'
END
go

---OutPut

Server Details

2011-08-31T23:56:00.000-07:00

To Get Server Details

select CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) AS 'Server Name',
'' as 'IP Address',
(select @@version) as SQL_Name,
CAST(SERVERPROPERTY('Edition') AS VARCHAR(200)) AS 'Server Edition',
CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(15)) AS 'Version',
CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR(15)) AS 'Service Pack',
Getdate() as 'As On',
''As Purpose,
CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR(17)) AS 'Cluster Name'

--- IsClustered
SELECT CONVERT(char(20), SERVERPROPERTY('IsClustered'));
--- Physical Name
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
--- Logical Name
SELECT SERVERPROPERTY('ComputerNameLogicalNetBIOS');
-- Detrimin Bit version
select serverproperty('edition')
-- Detrimin Product Version
SELECT SERVERPROPERTY('ProductVersion')
-- Detrimin License
SELECT SERVERPROPERTY('LicenseType') AS [LicenseType]
SELECT SERVERPROPERTY('NumLicenses') AS [NumLicenses]

--- All Details about OS
exec master..xp_cmdshell 'systeminfo'
exec xp_msver

--- Server Details
select * from sys.dm_os_sys_info

--- To Get The OS Version
master..xp_msver
master..xp_cmdshell 'netsh diag SHOW os /p'
select right(@@version, 44)

---- Important Query for Server Details
select @@servername
select @@version
SELECT SERVERPROPERTY ('MachineName') As ServerName
SELECT SERVERPROPERTY ('Edition') As Edition
SELECT SERVERPROPERTY ('ProductVersion')As ProductVersion
SELECT SERVERPROPERTY('ProductLevel')as ProductLevel
SELECT CONVERT(char(20), SERVERPROPERTY('BuildClrVersion'));
SELECT CONVERT(char(20), SERVERPROPERTY('Collation'));
SELECT CONVERT(char(20), SERVERPROPERTY('CollationID'));
SELECT CONVERT(char(20), SERVERPROPERTY('ComparisonStyle'));
SELECT CONVERT(char(20), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'));
SELECT CONVERT(char(20), SERVERPROPERTY('Edition'));
SELECT CONVERT(char(20), SERVERPROPERTY('EditionID'));
SELECT CONVERT(char(20), SERVERPROPERTY('EngineEdition'));
SELECT CONVERT(char(20), SERVERPROPERTY('InstanceName'));
SELECT CONVERT(char(20), SERVERPROPERTY('IsClustered'));
SELECT CONVERT(char(20), SERVERPROPERTY('IsFullTextInstalled'));
SELECT CONVERT(char(20), SERVERPROPERTY('IsIntegratedSecurityOnly'));
SELECT CONVERT(char(20), SERVERPROPERTY('IsSingleUser'));
SELECT CONVERT(char(20), SERVERPROPERTY('LCID'));
SELECT CONVERT(char(20), SERVERPROPERTY('LicenseType'));
SELECT CONVERT(char(20), SERVERPROPERTY('MachineName'));
SELECT CONVERT(char(20), SERVERPROPERTY('NumLicenses'));
SELECT CONVERT(char(20), SERVERPROPERTY('ProcessID'));
SELECT CONVERT(char(20), SERVERPROPERTY('ProductVersion'));
SELECT CONVERT(char(20), SERVERPROPERTY('ProductLevel'));
SELECT CONVERT(char(20), SERVERPROPERTY('ResourceLastUpdateDateTime'));
SELECT CONVERT(char(20), SERVERPROPERTY('ResourceVersion'));
SELECT CONVERT(char(20), SERVERPROPERTY('ServerName'));
SELECT CONVERT(char(20), SERVERPROPERTY('SqlCharSet'));
SELECT CONVERT(char(20), SERVERPROPERTY('SqlCharSetName'));
SELECT CONVERT(char(20), SERVERPROPERTY('SqlSortOrder'));
SELECT CONVERT(char(20), SERVERPROPERTY('SqlSortOrderName'));
SELECT CONVERT(char(20), SERVERPROPERTY('FilestreamShareName'));
SELECT CONVERT(char(200), SERVERPROPERTY('FilestreamConfiguredLevel'));
SELECT SERVERPROPERTY('FilestreamEffectiveLevel')

Trigger

2010-03-14T22:51:00.000-07:00

Triggering

Concept
Trigger that will run when someone updates the SalaryGrade within the specified table automatically the trigger will capture the previous salarygrade and insert into another table along with New and previous salarygrade..

--- Table 1 : Employee
Create Table Employee (
EmployeeID int not null
Constraint pk_Employee_EmployeeID primary key,
EmployeeName varchar(10) not null,
SalaryGrade varchar(10) not null,
Slary smallmoney not null
)
-------------------------------------------------------------
--- Table 2 : EmployeeChanges
Create Table EmployeeChanges (
ChangeID int identity(1,1) not null
Constraint pk_EmployeeChanges_ChangeID primary key,
ChangeDateTime datetime not null,
EmployeeName varchar(10) not null,
OldSalaryGrade varchar(10) not null,
NewSalaryGrade varchar(10) not null,
)
--------------------------------------------------------------
--- Before Update Insert to Another Table
CREATE Trigger tr_Update_RecordSalaryGradeChanges
On Employee
For Update As
declare @rowcount as int
declare @ChangeDateTime as datetime
declare @EmployeeName as varchar(10)
declare @OldSalaryGrade as smallmoney
declare @NewSalarygrade as smallmoney
If @@RowCount > 0
Begin
If update (Salarygrade)
Begin
begin transaction
select @ChangeDateTime = getdate(),
@EmployeeName = Inserted.EmployeeName,
@OldSalaryGrade = Deleted.Salarygrade,
@NewSalaryGrade = Inserted.Salarygrade
from Inserted inner join Deleted
on Inserted.EmployeeID = Deleted.EmployeeID
insert EmployeeChanges(ChangeDateTime, EmployeeName, OldSalaryGrade, NewSalarygrade)
values (@ChangeDateTime, @EmployeeName, @OldSalaryGrade, @NewSalarygrade)
set @rowcount = @@rowcount
if @@rowcount = 1
Begin
Commit transaction
End
Else
Begin
Rollback transaction
RaisError('Error recording to change table, contact admin', 16, 1)
End
End
End
Return
--- drop trigger tr_Update_RecordDeliveryChargeChanges
----------------------------------------------------------------
--- For Multiple Records Update in Employee Tables
Alter Trigger tr_Update_RecordSalaryGradeChanges
On Employee
For Update As

If update (salarygrade)
Begin

Insert EmployeeChanges(ChangeDateTime, EmployeeName, OldSalaryGrade, NewSalaryGrade)
Select getdate(), Inserted.EmployeeName,Deleted.SalaryGrade,Inserted.SalaryGrade
From Inserted inner join Deleted
on Inserted.EmployeeID = Deleted.EmployeeID

If @@error <> 0
Begin
RaisError('Error recording to change table, contact admin', 16, 1)
End
End
----------------------------------------------------------
--- For mail send while Update in Employee Table
/*
To ensure before this trigger configure your database mail.
for more information please visit db mail configuration Page :
http://sqlservertechnics.blogspot.com/2010/03/db-mail-configuration.html
*/
ALTER TRIGGER EmployeeUpdateMail
-- and the table is CustomerInfo
ON Employee
-- trigger is fired when an update is made for the table
FOR UPDATE
AS
-- holds the EmployeeID so we know which Customer was updated
declare @EmployeeID varchar(10)
-- holds the body of the email
declare @body varchar(2000)
-- holds the old customer name which has been changed
declare @EmployeeName varchar(10)
-- holds the new customer name
declare @EmployeeNewName varchar(10)
-- gets the previous customer first name that was deleted
SELECT @EmployeeID = EmployeeID,
@EmployeeName = d.EmployeeName
FROM deleted d
-- gets the new customer first name
SELECT @EmployeeNewName = EmployeeName
FROM inserted

SET @body = 'Employee with ID= ' + @EmployeeID + ' has been updated
with previous Employee Name is : ' + @EmployeeName + 'and the
New Employee Name is : '+ @EmployeeNewName
--- xp_sendmail is the extended sproc used to send the mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Database Email',
@recipients = 'adscxdasf@dsfasdf.com',
@body = @body,
@subject = 'Employee Information Updated'
GO
-----------------------------------------------------------
-- Workouts
Select * from dbo.Employee
Select * from dbo.EmployeeChanges
--- delete from dbo.EmployeeChanges

update Employee set salarygrade='FinalMail'
where EmployeeID in (1,2)

update Employee set EMPLOYEENAME='FinalMail5'
where EmployeeID in (1)
-----------------------------------------------------------

Error and Troubleshoot

2010-03-10T00:11:00.000-08:00

Error and Troubleshoot in SQL server 2005

-- Errors and Error methods
BEGIN TRY
-- SELECT 1/0--Evergreen divide by zero example!
select FirstName/100 from customer
END TRY

BEGIN CATCH
SELECT 'There was an error! ' + ERROR_MESSAGE()
SELECT ERROR_NUMBER()
SELECT ERROR_SEVERITY()
SELECT ERROR_STATE()
SELECT ERROR_PROCEDURE()
SELECT ERROR_LINE()
SELECT ERROR_MESSAGE()
RETURN
END CATCH;

-- Error Query

select distinct e.department_id + ' - ' +d.department_name as [Department Name]
from employee e
left outer join Department as d on d.department_id=e.department_id

/*-- Error Message
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.
*/

-- Correct Query
select distinct
CAST(e.department_id AS NVARCHAR) + ' - ' +d.department_name as [Department Name]
from employee e
left outer join Department as d on d.department_id=e.department_id

_______________________________________________________________

SQL Server Back Up and History

2010-03-04T18:02:00.000-08:00

SQL Server Back Up and History

-- BACKUP SERVICE MASTER KEY
BACKUP SERVICE MASTER KEY TO FILE = 'C:\backup\servicemasterkey_backup.key'
ENCRYPTION BY PASSWORD = 'password@5'

-- RESTORE SERVICE MASTER KEY
RESTORE SERVICE MASTER KEY FROM FILE = 'C:\backup\servicemasterkey_backup.key'
DECRYPTION BY PASSWORD = 'password@5'
FORCE

-- BACKUP MASTER KEY
BACKUP MASTER KEY TO FILE = 'C:\backup\masterkey_backup.key'
ENCRYPTION BY PASSWORD = 'password@5'

-- RESTORE MASTER KEY
RESTORE MASTER KEY FROM FILE = 'C:\backup\masterkey_backup.key'
DECRYPTION BY PASSWORD = 'password@5'
ENCRYPTION BY PASSWORD = 'password@5'
FORCE

-- To Back up Database
BACKUP DATABASE [Employee] TO DISK = N'C:\Backup\Employee.bak'
WITH NOFORMAT, NOINIT, NAME = N'Employee-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

-- To Restore Databse
RESTORE DATABASE [Employee] FROM DISK = N'C:\Backup\Employee.bak'
WITH FILE = 1,
MOVE N'Employee_log'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Employee_log.LDF',
NOUNLOAD, REPLACE, STATS = 10
GO

--- SQL Server Script to Retrievw Job History
select job_name, run_datetime, run_duration
from
(
select job_name, run_datetime,
SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
SUBSTRING(run_duration, 5, 2) AS run_duration
from
(
select DISTINCT
j.name as job_name,
run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +
(run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
from msdb..sysjobhistory h
inner join msdb..sysjobs j
on h.job_id = j.job_id
) t
) t
order by job_name, run_datetime

-- or

SELECT sj.name,
sja.run_requested_date,
CONVERT(VARCHAR(12), sja.stop_execution_date-sja.start_execution_date, 114) Duration
FROM msdb.dbo.sysjobactivity sja
INNER JOIN msdb.dbo.sysjobs sj
ON sja.job_id = sj.job_id
WHERE sja.run_requested_date IS NOT NULL
ORDER BY sja.run_requested_date desc

-- To Retrievw Path and Size of the Databas Backup
SELECT top 10 s.database_name,
m.physical_device_name,
s.backup_size,
cast(s.backup_size/1000000 as varchar(14))+' '+'MB' as bkSize,
CAST (DATEDIFF(second,s.backup_start_date , s.backup_finish_date)AS VARCHAR(4))+' '+'Seconds' TimeTaken,
s.backup_start_date,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m
ON s.media_set_id = m.media_set_id
-- Optional Condition
-- WHERE s.database_name = 'BackupReport'
-- ORDER BY database_name, backup_start_date, backup_finish_date

-- To check SQL Server FULL database backups are successful or not
SELECT database_name, backup_start_date, type, *
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'D'
ORDER BY backup_set_id DESC
GO

-- To check SQL Server Transaction Log backups are successful or not
SELECT database_name, backup_start_date, type, *
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -344, GETDATE()) AND GETDATE()
AND Type = 'L'
ORDER BY backup_set_id DESC
GO

-- To check SQL Server Differential Backups are successful or not
SELECT database_name, backup_start_date, type, *
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'I'
ORDER BY backup_set_id DESC
GO

-- To check SQL Server File\File Group Backups are successful or not
SELECT database_name, backup_start_date, type, *
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'F'
ORDER BY backup_set_id DESC
GO

Operator in SQL Server 2005

2010-03-03T17:23:00.000-08:00

--*************************************--
-- ALL About Operator --
--*************************************--

-- ADD or Create New Operator
USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'Dbmail',
@enabled=1,
@pager_days=0,
@email_address=N'testmailfromdb@domin.com',
@pager_address=N'',
@netsend_address=N''
GO

-- UPDATE operator
USE [msdb]
GO
EXECUTE msdb.dbo.sp_update_operator @name = 'Dbmail',
@email_address = 'testmailfromdb2@domin.com'
GO

-- DELETE operator with the same name.
EXECUTE msdb.dbo.sp_delete_operator @name = N'Dbmail'

-- To DISABLE the operator
EXEC msdb.dbo.sp_update_operator @name=N'Dbmail',
@enabled=0

-- To ENABLE the operator
EXEC msdb.dbo.sp_update_operator @name=N'Dbmail',
@enabled=1

DB Mail Configuration

2010-03-02T20:16:00.000-08:00

Database Mail Configuration in SQL Server 2005

-- Enabling Database Mail
sp_configure 'show advanced', 1;
GO
RECONFIGURE;
GO

sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE;
GO

-- Checking Database Mail is running or not
EXEC msdb.dbo.sysmail_help_status_sp;

-- Starting Database Mail
EXEC msdb.dbo.sysmail_start_sp;

-- Stopping Database Mail
EXEC msdb.dbo.sysmail_stop_sp;

-- Checking Mail Queue Status
-- The state of mail queue should be 'RECEIVES_OCCURRING'
-- The column length gives the number of mails in the queue
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';

-- Adding Database Mail Account
-- Add valid smtp mailserver_name
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Production Database Administration Profile',
@description = 'Administrative e-mail account',
@email_address = 'dfsafasfddffasd@yahoo.com',
@display_name = 'Automated Admin Mailer',
@mailserver_name = 'mail.yahoo.com' ;

-- Adding Database Mail Profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Production Database Administration Profile',
@description = 'Administrative mail profile' ;

-- Adding Database Mail Account to Profile
/* Add unique integer Sequence number for respective account in a profile. Mail is sent through the account based on the order of sequence number. */
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Production Database Administration Profile',
@account_name = 'Production Database Administration Account',
@sequence_number = 1 ;

-- Grant access to the profile
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Production Database Administration Profile',
@principal_name = 'public',
@is_default = 1

-- Listing all Database Mail Accounts
EXECUTE msdb.dbo.sysmail_help_account_sp ;

-- Listing all Database Mail Profiles
EXECUTE msdb.dbo.sysmail_help_profile_sp;

-- Listing all Database Mail Profiles with their respective Accounts
EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

--To find out the profile name
SELECT [profile_id]
,[name]
,[description]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_profile]

-- Deleting Database Mail Account
-- Pass Account name or account id as the parameter
EXECUTE msdb.dbo.sysmail_delete_account_sp
@account_name = 'Production Database Administration Account' ;

EXECUTE msdb.dbo.sysmail_delete_account_sp
@account_id = 1;

-- Deleting Database Mail Profile
-- Pass Profile name or profile id as the parameter
EXECUTE msdb.dbo.sysmail_delete_profile_sp
@profile_name = 'Production Database Administration Profile' ;

EXECUTE msdb.dbo.sysmail_delete_profile_sp
@profile_id = 1;

-- Deleting Database Mail Account from Profile
EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp
@profile_name = 'Production Database Administration Profile',
@account_name = 'Production Database Administration Account' ;

-- Updating Database Mail Account
EXECUTE msdb.dbo.sysmail_update_account_sp
@account_id = 1,
@account_name = 'DBA',
@email_address = 'dsafffffewew@yahoo.com',
@display_name = 'Admin';

-- Updating Database Mail Account
EXECUTE msdb.dbo.sysmail_update_profile_sp
@profile_id = 1,
@profile_name = 'DBA'

-- Sending Database Mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Production Database Administration Profile',
@recipients = 'dafskjkj@yahoo.com',
@copy_recipients = 'Abcjkdsfah@yahoo.com',
@blind_copy_recipients ='dalasldkfa@yahoo.com',
@body_format = 'TEXT',
@body = 'Test Email',
@subject = 'Automated Test Mail';

-- Send file attachments
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Production Database Administration Profile',
@recipients = 'dsafsdfasdf@yahoo.com',
@file_attachments = 'D:\File1.txt;D:\File2.txt'
@body = 'Test File Attachments',
@subject = 'Files Attached';

-- Send mail with query results
/*Specify value 0 for @attach_query_result_as_file for including the contents in body and specify value 1 to send the results as attachment*/
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Production Database Administration Profile',
@recipients = 'asdfjjhdsaf@yahoo.com',
@query = 'SELECT COUNT(*) FROM msdb.dbo.DTA_input' ,
@subject = 'Imported Records Count',
@attach_query_result_as_file = 1 ;

-- Sending HTML Mail
DECLARE @tableHTML VARCHAR(MAX) ;

SET @tableHTML =
'

HTML Mail

' +
'' +
'' +
'

Table Header

' ;

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Production Database Administration Profile',
@recipients='fsdfgdsgds@yahoo.com',
@subject = 'HTML mail',
@body = @tableHTML,
@body_format = 'HTML' ;

-- Checking Mail Delivery status
-- Delivery Status can be viewed from column sent_status
-- Check all mails AND Clear histroy or allitems
select * from msdb.dbo.sysmail_allitems order by last_mod_date desc
-- DELETE from msdb.dbo.sysmail_allitems

-- Check sent mails and clear
select * from msdb.dbo.sysmail_sentitems order by last_mod_date desc
-- DELETE from msdb.dbo.sysmail_sentitems
-- Check failed mails
select * from msdb.dbo.sysmail_faileditems order by last_mod_date desc
-- If you want to clear run this script
-- DELETE from msdb.dbo.sysmail_faileditems
-- Checking Failed Mail Reason
-- Column Description returns the Failure reason
select * from msdb.dbo.sysmail_event_log where mailitem_id ='1507'
DELETE from msdb.dbo.sysmail_event_log

-- Monitoring Database Mail run msdb DB
SELECT * FROM msdb.dbo.sysmail_allitems where recipients='dsaffsdfds@yahoo.com'
--delete FROM msdb.dbo.sysmail_allitems where recipients='dsafsadf@yahoo.com'
SELECT * FROM msdb.dbo.sysmail_event_log
SELECT * FROM msdb.dbo.sysmail_sentitems
SELECT * FROM msdb.dbo.sysmail_faileditems
SELECT * FROM msdb.dbo. sysmail_mailattachments
SELECT count(*) FROM msdb.dbo.sysmail_allitems
SELECT * FROM msdb.dbo.sysmail_sentitems
SELECT count(*) FROM msdb.dbo.sysmail_unsentitems
SELECT * FROM msdb.dbo.sysmail_unsentitems

-- Troubleshooting DB Mail --

/*
Msg 14650, Level 16, State 1, Procedure sp_send_dbmail, Line 72
Service Broker message delivery is not enabled in this database. Use the ALTER DATABASE statement to enable Service Broker message delivery.
*/
/* A value of 0 indicates that Service Broker message delivery is not activated in the msdb database */
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'
--- check members of the DatabaseMailUserRole
EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole'
EXEC msdb.dbo.sysmail_help_principalprofile_sp
--- check status of Database Mail
EXECUTE msdb.dbo.sysmail_help_status_sp ;
---start Database Mail in a mail host database
EXECUTE msdb.dbo.sysmail_start_sp

EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail'
-- Kill all connection to msdb database and run:
-- stop the agent service, run the query, and then restart the service.
ALTER DATABASE msdb SET ENABLE_BROKER

-- ALTER DATABASE MSDB SET NEW_BROKER

-- You can set is_broker_enabled column value to 1 by executing the following command
ALTER DATABASE Employee SET NEW_BROKER

ALTER DATABASE Employee SET ENABLE_BROKER

ALTER DATABASE Employee SET ERROR_BROKER_CONVERSATIONS

ALTER DATABASE Employee SET TRUSTWORTHY ON

ALTER DATABASE Employee SET DISABLE_BROKER

SHRINK LOG FILE

2010-02-28T20:04:00.001-08:00

SHRINK LOG FILE in SQL Server 2005

-- To find out the Free Disk Space
xp_fixeddrives

-- Before Check the DB Size
DBCC SQLPERF(LOGSPACE)

-- Want to see the current db .mdf and .ldf file run this script
SELECT file_id, name FROM sys.database_files
-- or if you know the database name (ex: db Name : Employee)
SELECT file_id, name FROM employee.sys.database_files

--Syntax to Shrink LoG file
BACKUP LOG WITH TRUNCATE_ONLY

DBCC SHRINKFILE (, Size)
-- Size is in MB

-- Shrink LoG File Example:-

BACKUP LOG Employee WITH TRUNCATE_ONLY

DBCC SHRINKFILE (Employee_log, 500)
-- It will shrink 500 MB Size
-- Example Before Shrink your Employee DB size 1000MB
-- After Shrink your Employee DB Size would Be 500MB

-- To find the DB Size Run the following Command
DBCC SQLPERF(LOGSPACE)

--- Shrink Database
USE master;
DBCC SHRINKDATABASE (Employee, 10);
GO

-- Increase Transaction Log size
ALTER DATABASE Employee
MODIFY FILE
(NAME = Employee_log,SIZE = 80MB)

--- Trouble Shot
If Suppose you got the error message like this you need to change the log size which is higher than you previous log file size

Msg 5039, Level 16, State 1, Line 1
MODIFY FILE failed. Specified size is less than current size.

------------------------------------------------------------------------------------
-- To find out all drive space details and Free space details
SET NOCOUNT ON

IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveSpace')
DROP TABLE ##_DriveSpace

IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveInfo')
DROP TABLE ##_DriveInfo

DECLARE @Result INT
, @objFSO INT
, @Drv INT
, @cDrive VARCHAR(13)
, @Size VARCHAR(50)
, @Free VARCHAR(50)
, @Label varchar(10)

CREATE TABLE ##_DriveSpace
(
DriveLetter CHAR(1) not null
, FreeSpace VARCHAR(10) not null

)

CREATE TABLE ##_DriveInfo
(
DriveLetter CHAR(1)
, TotalSpace bigint
, FreeSpace bigint
, Label varchar(10)
)

INSERT INTO ##_DriveSpace
EXEC master.dbo.xp_fixeddrives

-- Iterate through drive letters.
DECLARE curDriveLetters CURSOR
FOR SELECT driveletter FROM ##_DriveSpace

DECLARE @DriveLetter char(1)
OPEN curDriveLetters

FETCH NEXT FROM curDriveLetters INTO @DriveLetter
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN

SET @cDrive = 'GetDrive("' + @DriveLetter + '")'

EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @objFSO OUTPUT

IF @Result = 0

EXEC @Result = sp_OAMethod @objFSO, @cDrive, @Drv OUTPUT

IF @Result = 0

EXEC @Result = sp_OAGetProperty @Drv,'TotalSize', @Size OUTPUT

IF @Result = 0

EXEC @Result = sp_OAGetProperty @Drv,'FreeSpace', @Free OUTPUT

IF @Result = 0

EXEC @Result = sp_OAGetProperty @Drv,'VolumeName', @Label OUTPUT

IF @Result <> 0

EXEC sp_OADestroy @Drv
EXEC sp_OADestroy @objFSO

SET @Size = (CONVERT(BIGINT,@Size) / 1048576 )

SET @Free = (CONVERT(BIGINT,@Free) / 1048576 )

INSERT INTO ##_DriveInfo
VALUES (@DriveLetter, @Size, @Free, @Label)

END
FETCH NEXT FROM curDriveLetters INTO @DriveLetter
END

CLOSE curDriveLetters
DEALLOCATE curDriveLetters

PRINT 'Drive information for server ' + @@SERVERNAME + '.'
PRINT ''

-- Produce report.
SELECT DriveLetter
, Label
, FreeSpace AS [FreeSpace MB]
, (TotalSpace - FreeSpace) AS [UsedSpace MB]
, TotalSpace AS [TotalSpace MB]
, ((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) AS [Percentage Free]

FROM ##_DriveInfo
ORDER BY [DriveLetter] ASC
GO

DROP TABLE ##_DriveSpace
DROP TABLE ##_DriveInfo

----- Trouble Shot -----------------
/* -- Error Message

Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
*/

use master
go
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

------------------------------------------------------------------------------------

Date format in SQL Server 2005

2010-02-04T18:37:00.001-08:00

Select getdate() -- 2010-02-05 10:03:44.527

-- To get all date format
select CONVERT(VARCHAR(12),getdate(),100) +' '+ 'Date -100- MMM DD YYYY' -- Feb 5 2010
union
select CONVERT(VARCHAR(10),getdate(),101) +' '+ 'Date -101- MM/DDYYYY'
Union
select CONVERT(VARCHAR(10),getdate(),102) +' '+ 'Date -102- YYYY.MM.DD'
Union
select CONVERT(VARCHAR(10),getdate(),103) +' '+ 'Date -103- DD/MM/YYYY'
Union
select CONVERT(VARCHAR(10),getdate(),104) +' '+ 'Date -104- DD.MM.YYYY'
Union
select CONVERT(VARCHAR(10),getdate(),105) +' '+ 'Date -105- DD-MM-YYYY'
Union
select CONVERT(VARCHAR(11),getdate(),106) +' '+ 'Date -106- DD MMM YYYY' --ex: 03 Jan 2007
Union
select CONVERT(VARCHAR(12),getdate(),107) +' '+ 'Date -107- MMM DD,YYYY' --ex: Jan 03, 2007
union
select CONVERT(VARCHAR(12),getdate(),109) +' '+ 'Date -108- MMM DD YYYY' -- Feb 5 2010
union
select CONVERT(VARCHAR(12),getdate(),110) +' '+ 'Date -110- MM-DD-YYYY' --02-05-2010
union
select CONVERT(VARCHAR(10),getdate(),111) +' '+ 'Date -111- YYYY/MM/DD'
union
select CONVERT(VARCHAR(12),getdate(),112) +' '+ 'Date -112- YYYYMMDD' -- 20100205
union
select CONVERT(VARCHAR(12),getdate(),113) +' '+ 'Date -113- DD MMM YYYY' -- 05 Feb 2010

SELECT convert(varchar, getdate(), 20) -- 2010-02-05 10:25:14
SELECT convert(varchar, getdate(), 23) -- 2010-02-05
SELECT convert(varchar, getdate(), 24) -- 10:24:20
SELECT convert(varchar, getdate(), 25) -- 2010-02-05 10:24:34.913
SELECT convert(varchar, getdate(), 21) -- 2010-02-05 10:25:02.990

---==================================
-- To get the time
select CONVERT(VARCHAR(12),getdate(),108) +' '+ 'Date -108- HH:MM:SS' -- 10:05:53

select CONVERT(VARCHAR(12),getdate(),114) +' '+ 'Date -114- HH:MM:SS:MS' -- 10:09:46:223
SELECT convert(varchar, getdate(), 22) -- 02/05/10 10:23:11 AM
----=============================================
SELECT getdate()+1
SELECT month(getdate())+1
SELECT year(getdate())+1

Test from Yahoo

2009-12-25T07:59:00.001-08:00

Test From Yahoo

Sathish

test

2009-12-09T05:47:00.001-08:00

test

Restore

2009-11-30T17:41:00.000-08:00

R E S T O R E

Tuesday, December 01, 2009

Method 1:-

RESTORE DATABASE NewDatabaseName

FROM DISK='D:\YourBackupfilename.bak'

WITH MOVE'LOgicalName of the SOurceDatafile' TO 'D:\NewDatabase.mdf',

MOVE 'Logicalname of sourcelogfile' TO'D:\NewDatabase_log.ldf'

Method 2:-

RESTORE DATABASE ntest_new FROM DISK = 'C:\temp\test.bak'

WITH

MOVE 'test' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\ntest_new.mdf' ,

MOVE 'test_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\ntest_new_log.ldf'

Method 3:-

Retrive the Logical file name of the database from backup.
RESTORE FILELISTONLY
FROM DISK = 'D:BackUpYourBaackUpFile.bak'
GO

Method 4:-

RESTORE DATABASE YourDB
FROM DISK = 'D:BackUpYourBaackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.mdf'

Method 5:-

RESTORE DATABASE Employee_Data_TSQL
FROM DISK='d:\Employee_Data.bak'
WITH
MOVE 'Employee_Data' TO 'D:\TSQL\Employee_Data.mdf',
MOVE 'Employee_Data_log' TO 'D:\TSQL\Employee_Data_log.ldf'

Method 6:-

RESTORE DATABASE Employee_Data_TSQL
FROM DISK='d:\Employee_Data.bak'
WITH
MOVE 'Employee_Data' TO 'D:\TSQL\Employee_Data.mdf',
MOVE 'Employee_Data_log' TO 'D:\TSQL\Employee_Data_log.ldf', STATS=10

2009-11-30T01:39:00.000-08:00

Main Menu

SQL Server FaQ

SQL FAQ 2

SQL Server Important Web Sites

Important SQL Query

Joins

2009-11-25T23:01:00.000-08:00

A l l A b o u t J o i n s

Step 1: Create Employee Table

USE [Test]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Employee](

[EmployeeID] [int] NOT NULL,

[EmployeeName] [nchar](50) COLLATE Latin1_General_CI_AS NOT NULL,

[ContactID] [int] NULL,

[ManagerID] [int] NULL,

[MaritalStatus] [nchar](1) COLLATE Latin1_General_CI_AS NULL,

[HireDate] [datetime] NULL,

CONSTRAINT [PK_FK_Employee_EmployeeID] PRIMARY KEY CLUSTERED

(

[EmployeeID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key for employee rows' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Employee', @level2type=N'COLUMN', @level2name=N'EmployeeID'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identifies the employee in the Contact table. Foreign key to Contact.ContactID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Employee', @level2type=N'COLUMN', @level2name=N'ContactID'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'M = Married S = Single' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Employee', @level2type=N'COLUMN', @level2name=N'MaritalStatus'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date on which the employee was hired' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Employee', @level2type=N'COLUMN', @level2name=N'HireDate'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Manager to whom the employee is assigned. Foreign key to Employee.EmployeeID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Employee', @level2type=N'CONSTRAINT', @level2name=N'PK_FK_Employee_EmployeeID'

USE [Test]

ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Contact] FOREIGN KEY([ContactID])

REFERENCES [dbo].[Contact] ([ContactID])

ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Manager] FOREIGN KEY([ManagerID])

REFERENCES [dbo].[Manager] ([ManagerID])

Step 2: Create EmployeeAddress Table

USE [Test]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[EmployeeAddress](

[EmployeeID] [int] NOT NULL,

[AddressID] [int] NOT NULL,

[Address] [nchar](70) COLLATE Latin1_General_CI_AS NOT NULL,

CONSTRAINT [PK_EmployeeAddress] PRIMARY KEY CLUSTERED

(

[AddressID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Employee identification number. Foreign key to Employee.EmployeeID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'EmployeeAddress', @level2type=N'COLUMN', @level2name=N'EmployeeID'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Employee address identification number. Foreign key to Address.AddressID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'EmployeeAddress', @level2type=N'COLUMN', @level2name=N'AddressID'

USE [Test]

ALTER TABLE [dbo].[EmployeeAddress] WITH CHECK ADD CONSTRAINT [FK_EmployeeAddress_Employee] FOREIGN KEY([EmployeeID])

REFERENCES [dbo].[Employee] ([EmployeeID])

Step 3:

Insert Some values for both Employee and Employeeaddress tables

Step 4 Join query Workouts

Inner Join Examples

SELECT E.EmployeeID, E.EmployeeName,E.ContactID,A.AddressID, A.address

FROM Employee E INNER JOIN

Employeeaddress A ON A.EmployeeID = E.EmployeeID

WHERE A.addressID > 10 -- or = 10 or <>

ORDER BY E.Employeename, A.address

Inner Natural Join Examples

SELECT E.*, A.addressID, A.address FROM Employee E

INNER JOIN

Employeeaddress A ON A.EmployeeID = E.EmployeeID

Inner Equi Join Example:

SELECT * FROM Employee E INNER JOIN

EmployeeAddress A ON A.EmployeeID = E.EmployeeID

Who is belongs to which manager

select e1.Managername 'Manager',e2.employeename 'Employee'

from Manager e1 join employee e2

on e1.managerid=e2.ManagerID

Left Outer Join Example

SELECT E.Employeename, E.ManagerID, C.Contactname

FROM employee E LEFT OUTER JOIN contact C

ON E.contactID = C.contactid ORDER BY E.employeename

Right Outer Join Example

SELECT E.Employeename, E.ManagerID, C.Contactname

FROM employee E Right OUTER JOIN contact C

ON E.contactID = C.contactid ORDER BY E.employeename

--ORDER BY E.employeename, A.AU_FNAME

FULL Outer Join Example

SELECT E.Employeename, E.ManagerID, C.Contactname

FROM employee E FULL OUTER JOIN contact C

ON E.contactID = C.contactid ORDER BY E.employeename

--ORDER BY E.employeename, A.AU_FNAME

CROSS Join Example

SELECT Employeename, EmployeeID, ContactName

FROM Employee CROSS JOIN Contact

ORDER BY Employeename

LEFT OUTER JOIN that even returns the employees without managers

SELECT t1.EmployeeName [Employee], COALESCE(t2.EmployeeName, 'No manager') [Manager]

FROM Employee t1

LEFT OUTER JOIN

Employee t2

t1.ManagerID = t2.EmployeeID

SQL Server Security Audit

2009-11-23T22:39:00.000-08:00

SQL Server Security Audit

To find out what login accounts are members of this role [Working]

SELECT c.name AS Sysadmin_Server_Role_Members
FROM sys.server_principals a
INNER JOIN sys.server_role_members b
ON a.principal_id = b.role_principal_id AND a.type = 'R'
AND a.name ='sysadmin'
INNER JOIN sys.server_principals c
ON b.member_principal_id = c.principal_id

Result

To get a list of server level permissions for logins

SELECT a.name AS Login_Name,
a.type_desc AS LoginType,
b.permission_name AS Permission
FROM sys.server_principals a
INNER JOIN sys.server_permissions b
ON a.principal_id = b.grantee_principal_id
WHERE a.type <> 'R' -- Excludes Server Role
AND a.name NOT LIKE '##MS_%' -- Excludes system level certificate mapped logins
AND a.name <> 'sa' -- Excludes the obvious system administrator
AND b.state = 'G' -- Granted
ORDER BY a.name

Result

To find out which logins have master as the default database

USE master
GO
SELECT name as LoginName, type_desc AS LoginType
FROM sys.server_principals
WHERE type <> 'R' -- Excludes Server Role
AND name NOT LIKE '##MS_%' -- Excludes system level certificate mapped logins
AND name <> 'sa' -- Excludes the obvious system administrator
AND default_database_name = 'master'

DBCC Commands

2009-11-23T18:18:00.000-08:00

Important DBCC Command

-- Shrink Database

USE [Employee]
GO
DBCC SHRINKDATABASE(N'Employee', 4 )
GO

-- Find out the constraints in the current database

DBCC CHECKCONSTRAINTS

-- Find out the constraints in the particular table

DBCC CHECKCONSTRAINTS ('table1')

DBCC CHECKCONSTRAINTS ('Employee')

-- To Clean the table space after a column is dropped from the database 
DBCC CLEANTABLE ('MyDatabase', 'MyTable')
DBCC CLEANTABLE ('Company', 'Employee')



-- To Find the LogSpace
DBCC SQLPERF(LOGSPACE)
Exec ('DBCC SqlPerf (logspace)')

DBCC SqlPerf (UmsStats)
Exec ('DBCC SqlPerf (UmsStats)')

-- To retrivew the servers resources wait types
DBCC SqlPerf (WaitStats)
Exec ('DBCC SqlPerf (WaitStats)')

-- Retrieves information about your server’s outstanding
DBCC SqlPerf (IoStats)
Exec ('DBCC SqlPerf (IoStats)')

-- Retrieves information about your servers read-ahead statistics
DBCC SqlPerf (RaStats)
Exec ('DBCC SqlPerf (RaStats)')

-- Retrieves information for the server’s currently running threads
DBCC SqlPerf (Threads)
Exec ('DBCC SqlPerf (Threads)')

SQL Server Security

2009-11-22T18:33:00.000-08:00

==========================================================
Welcome to SQL Server 2005 Security Part

==========================================================

SQL Server Security

OBJECTIVE

SQL Server includes a variety of precise, configurable security features. These features empower administrators to implement defense-in-depth that is optimized for the specific security risks of their environment. This topic provides links to help you locate the security information that you need in the SQL Server Database Engine.

Securing SQL Server

Securing SQL Server can be viewed as a series of steps, involving four areas: the platform, authentication, objects (including data), and applications that access the system. The following topics will guide you through creating and implementing an effective security plan.

Principals

Principals are entities that can request SQL Server resources. Like other components of the SQL Server authorization model, principals can be arranged in a hierarchy. The scope of influence of a principal depends on the scope of the definition of the principal: Windows, server, database; and whether the principal is indivisible or a collection. A Windows Login is an example of an indivisible principal, and a Windows Group is an example of a principal that is a collection. Every principal has a security identifier (SID).

Windows-level principals

• Windows Domain Login

• Windows Local Login

SQL Server-level principal

• SQL Server Login

Database-level principals

• Database User

• Database Role

• Role

The SQL Server SA Login

The SQL Server sa log in is a server-level principal. By default, it is created when an instance is installed. In SQL Server 2005 and SQL Server 2008, the default database of sa is master. This is a change of behavior from earlier versions of SQL Server.

Public Database Role

Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable, the user inherits the permissions granted to public on that securable.

Client and Database Server

By definition, a client and a database server are security principals and can be secured. These entities can be mutually authenticated before a secure network connection is established. SQL Server supports the Kerberos authentication protocol, which defines how clients interact with a network authentication service.

Database Users

A database user is a principal at the database level. Every database user is a member of the public role.

Guest user

By default, the database includes a guest user when a database is created. Permissions granted to the guest user are inherited by users who do not have a user account in the database.

The guest user cannot be dropped, but it can be disabled by revoking its CONNECT permission. The CONNECT permission can be revoked by executing REVOKE CONNECT FROM GUEST within any database other than master or tempdb.

Application Roles

An application role is a database principal that enables an application to run with its own, user-like permissions. You can use application roles to enable access to specific data to only those users who connect through a particular application. Unlike database roles, application roles contain no members and are inactive by default. Application roles work with both authentication modes. Application roles are enabled by using sp_setapprole, which requires a password. Because application roles are a database-level principal, they can access other databases only through permissions granted in those databases to guest. Therefore, any database in which guest has been disabled will be inaccessible to application roles in other databases.

In SQL Server, application roles cannot access server-level metadata because they are not associated with a server-level principal. To disable this restriction and thereby allow application roles to access server-level metadata, set the global flag 4616.

Connecting with an Application Role

The following steps make up the process by which an application role switches security contexts:

A user executes a client application.
The client application connects to an instance of SQL Server as the user.
The application then executes the sp_setapprole stored procedure with a password known only to the application.
If the application role name and password are valid, the application role is enabled.
At this point the connection loses the permissions of the user and assumes the permissions of the application role.

The permissions acquired through the application role remain in effect for the duration of the connection.

Syntax

CREATE APPLICATION ROLE application_role_name

WITH PASSWORD = 'password'

[ , DEFAULT_SCHEMA = schema_name ]

Real Time

CREATE APPLICATION ROLE Emp_salary

WITH PASSWORD = '987G^bv876sPY)Y5m23',

DEFAULT_SCHEMA = Employee; GO

Example:

USE [Employee] GO

CREATE APPLICATION ROLE [EmployeeSelect]

WITH DEFAULT_SCHEMA = [guest],

PASSWORD = N'employee@123' GO

USE [Employee] GO

ALTER AUTHORIZATION ON SCHEMA::[db_owner]

TO [EmployeeSelect] GO

USE [Employee] GO

ALTER AUTHORIZATION ON SCHEMA::[Employee]

TO [EmployeeSelect] GO

Arguments
application_role_name
Specifies the name of the application role. This name must not already be used to refer to any principal in the database.
PASSWORD = 'password'
Specifies the password that database users will use to activate the application role. You should always use strong passwords. password must meet the Windows password policy requirements of the computer that is running the instance of SQL Server.
DEFAULT_SCHEMA = schema_name
Specifies the first schema that will be searched by the server when it resolves the names of objects for this role. If DEFAULT_SCHEMA is left undefined, the application role will use DBO as its default schema. schema_name can be a schema that does not exist in the database.

User-Schema Separation
The behavior of schemas changed in SQL Server 2005. Schemas are no longer equivalent to database users; each schema is now a distinct namespace that exists independently of the database user who created it. In other words, a schema is simply a container of objects. A schema can be owned by any user, and its ownership is transferable.
New Behavior
The separation of ownership from schemas has important implications:
Ownership of schemas and schema-scoped securable is transferable.
Objects can be moved between schema's.
A single schema can contain objects owned by multiple database users.
Multiple database users can share a single default schema.
Permissions on schemas and schema-contained securable can be managed with greater precision than in earlier releases.
A schema can be owned by any database principal. This includes roles and application roles.
A database user can be dropped without dropping objects in a corresponding schema.
Code written for earlier releases of SQL Server may return incorrect results, if the code assumes that schemas are equivalent to database users.
Catalog views designed for earlier releases of SQL Server may return incorrect results. This includes sysobjects.
New Catalog Views
Schemas are explicit entities reflected in metadata; and as a result, schemas can only have one owner but a single user can own many schemas. This complex relationship is not reflected in the SQL Server 2000 system tables, so SQL Server 2005 introduced new catalog views, which accurately reflect the new metadata.
SQL Server 2005 and later catalog view
• sys.database_principals
• sys.schemas
• sys.server_principals
Default Schema's
In order to resolve the names of securables that are not fully qualified names, SQL Server 2000 uses name resolution to check the schema owned by the calling database user and the schema owned by dbo. Beginning with SQL Server 2005, each user has a default schema. The default schema can be set and changed by using the DEFAULT_SCHEMA option of CREATE USER or ALTER USER. If DEFAULT_SCHEMA is left undefined, the database user will have dbo as its default schema.
===================================================================
Managing Logins, Users, and Schemas
The following topics that show you how to manage SQL Server logins, database users, and database schemas.
• Create a SQL Server Login
• Create a Database User
• Create a Database Schema
Create a SQL Server Login
To create a SQL Server login that uses Windows Authentication (SQL Server Management Studio)

In SQL Server Management Studio, open Object Explorer and expand the folder of the server instance in which to create the new login.
Right-click the Security folder, point to New, and then click Login.
On the General page, enter the name of a Windows user in the Login name box.
Select Windows Authentication.
Click OK.
Examples:

Using Script
Syntax
CREATE LOGIN FROM WINDOWS; GO

Real Time
USE [master] GO
CREATE LOGIN [RESEARCH\Guest]
FROM WINDOWS WITH DEFAULT_DATABASE=[master] GO

To create a SQL Server login that uses SQL Server Authentication (SQL Server Management Studio)

1. In SQL Server Management Studio, open Object Explorer and expand the folder of the server instance in which to create the new login.
2. Right-click the Security folder, point to New, and then click Login.
3. On the General page, enter a name for the new login in the Login name box.
4. Select SQL Server Authentication. Windows Authentication is the more secure option.
5. Enter a password for the login.
6. Select the password policy options that should be applied to the new login. In general, enforcing password policy is the more secure option.
7. Click OK.

Examples:

Using Script
Syntax
CREATE LOGIN [LOGIN NAME] WITH PASSWORD = ''
DEFAULT_DATABASE=[Select Database name],
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON;
GO

Real Time
USE [master] GO CREATE LOGIN [RESEARCH\Guest]
WITH PASSWORD=N'winter@77' MUST_CHANGE,
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON
GO
===========================================================
Create a Database User
This session shows how to create a database user mapped to a SQL Server login. The topic assumes that a corresponding SQL Server login already exists.
To create a database user using SQL Server Management Studio
1. In SQL Server Management Studio, open Object Explorer and expand the Databases folder.
2. Expand the database in which to create the new database user.
3. Right-click the Security folder, point to New, and then click User.
4. On the General page, enter a name for the new user in the User name box.
5. In the Login name box, enter the name of a SQL Server login to map to the database user.
6. Click OK.

Using Script
Syntax
USE [database name] GO
CREATE USER [new user name] FOR LOGIN [login name]; GO
Real Time
USE [Employee]
GO
CREATE USER [Abcdef] FOR LOGIN [winter\victor]
GO
================================================================
Create a Database Schema
To create a schema by using SQL Server Management Studio
1.In SQL Server Management Studio, open Object Explorer and expand the Databases folder. 2.Expand the database in which to create the new database schema. 3.Right-click the Security folder, point to New, and then click Schema. 4.On the General page, enter a name for the new schema in the Schema name box. 5.In the Schema owner box, enter the name of a database user or role to own the schema. 6.Click OK.

Using Script
Syntax
USE [database name] GO
CREATE SCHEMA [new schema name]
AUTHORIZATION [new schema owner]; GO

Real Time
USE [Employee] GO
CREATE SCHEMA [Schemavictor]
AUTHORIZATION [ownvictor] GO
===================================================================
SERVER-LEVEL ROLES

To easily manage the permissions on SQL server, SQL Server provides several roles, which are security principals that group other principals. Roles are like groups in the Microsoft Windows operating system.

Server-level roles are also named fixed server roles because you cannot create new server-level roles. Server-level roles are server-wide in their permissions scope. You can add SQL Server logins, Windows accounts, and Windows groups into server-level roles. Each member of a fixed server role can add other logins to that same role. The following table shows the server-level roles and their capabilities.

Server-level roles are also named fixed server roles because you cannot create new server-level roles. Server-level roles are server-wide in their permissions scope.
You can add SQL Server logins, Windows accounts, and Windows groups into server-level roles. Each member of a fixed server role can add other logins to that same role.
The following table shows the server-level roles and their capabilities.

sysadmin
Description: Members of the sysadmin fixed server role can perform any activity in the server.
Example:
EXEC master..sp_addsrvrolemember @loginame = N'RESEARCH\victor, @rolename = N'sysadmin'
GO

Serveradmin
Description: Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.
Example:
EXEC master..sp_addsrvrolemember @loginame = N'RESEARCH\victor', @rolename = N'serveradmin'
GO

securityadmin
Description: Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions. Additionally, they can reset passwords for SQL Server logins.
Example:
EXEC master..sp_addsrvrolemember @loginame = N'RESEARCH\victor', @rolename = N'securityadmin'
GO

processadmin
Description: Members of the processadmin fixed server role can end processes that are running in an instance of SQL Server.
Example:
EXEC master..sp_addsrvrolemember @loginame = N'RESEARCH\victor', @rolename = N'processadmin'
GO

setupadmin
Description: Members of the setupadmin fixed server role can add and remove linked servers.
Example:
EXEC master..sp_addsrvrolemember @loginame = N'RESEARCH\victor', @rolename = N'setupadmin' GO

bulkadmin
Description: Members of the bulkadmin fixed server role can run the BULK INSERT statement.
Example:
EXEC master..sp_addsrvrolemember @loginame = N'RESEARCH\victor', @rolename = N'bulkadmin'
GO

diskadmin
Description: The diskadmin fixed server role is used for managing disk files.
Example:
EXEC master..sp_addsrvrolemember @loginame = N'RESEARCH\victor', @rolename = N'diskadmin'
GO

dbcreator
Description: Members of the dbcreator fixed server role can create, alter, drop, and restore any database.
Example:
EXEC master..sp_addsrvrolemember @loginame = N'RESEARCH\victor', @rolename = N'dbcreator'
GO

Public
Description: Every SQL Server login belongs to the public server role. When a server
principal has not been granted or denied specific permissions on a securable
object, the user inherits the permissions granted to public on that object.
Only assign public permissions on any object when you want the object
to be available to all users.
===================================================================
Database-Level Roles

To easily manage the permissions in your databases, SQL Server provides several roles which are security principals that group other principals. They are like groups in the Microsoft Windows operating system. Database-level roles are database-wide in their permissions scope.
There are two types of database-level roles in SQL Server: fixed database roles that are predefined in the database and flexible database roles that you can create.
Fixed database roles are defined at the database level and exist in each database. Members of the db_owner and db_security admin database roles can manage fixed database role membership. However, only members of the db_owner database role can add members to the db_owner fixed database role. There are also some special-purpose fixed database roles in themsdb database.
You can add any database account and other SQL Server roles into database-level roles. Each member of a fixed database role can add other logins to that same role.

db_owner

Description: Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database

db_securityadmin
Description: Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation.


db_accessadmin 
Description: Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.

db_backupoperator 
Description: Members of the db_backupoperator fixed database role can back up the database.

db_ddladmin
Description: Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.

db_datawriter
Description: Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

db_datareader
Description: Members of the db_datareader fixed database role can read all data from all user tables.

db_denydatawriter 
Description: Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.

db_denydatareader 
Description: Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.

==================================================
Password Policy

SQL Server provides several tools for configuring SQL Server features, connections, and services. This topic explains the tools that you can use to enable and disable features, such as stored procedures, Windows services, Web services, and remote client connectivity.
When it is running on Windows Server 2003 or later, SQL Server can use Windows password policy mechanisms.
SQL Server can apply the same complexity and expiration policies used in Windows Server 2003 to passwords used inside SQL Server. This functionality depends on theNetValidatePasswordPolicy API, which is only available in Windows Server 2003 and later versions.


Password Complexity
Password complexity policies are designed to deter brute force attacks by increasing the number of possible passwords. When password complexity policy is enforced, new passwords must meet the following guidelines:
• The password does not contain all or part of the account name of the user. Part of an account name is defined as three or more consecutive alphanumeric characters delimited on both ends by white space such as space, tab, and return, or any of the following characters: comma (,), period (.), hyphen (-), underscore (_), or number sign (#).
• The password is at least eight characters long.
• The password contains characters from three of the following four categories:
• Latin uppercase letters (A through Z)
• Latin lowercase letters (a through z)
• Base 10 digits (0 through 9)
• Non-alphanumeric characters such as: exclamation point (!), dollar sign ($), number sign (#), or percent (%)
Passwords can be up to 128 characters long. You should use passwords that are as long and complex as possible.


Password Expiration
Password expiration policies are used to manage the lifespan of a password.

When SQL Server enforces password expiration policy, users are reminded

to change old passwords, and accounts that have expired passwords are disabled.


Policy Enforcement
The enforcement of password policy can be configured separately for each SQL Server login. Use ALTER LOGIN (Transact-SQL) to configure the password policy options of a SQL Server login. The following rules apply to the configuration of password policy enforcement:

• When CHECK_POLICY is changed to ON, the following behaviors occur:
• CHECK_EXPIRATION is also set to ON unless it is explicitly set to OFF.
• The password history is initialized with the value of the current password hash.
• When CHECK_POLICY is changed to OFF, the following behaviors occur:
• CHECK_EXPIRATION is also set to OFF.
• The password history is cleared.
• The value of lockout_time is reset.
Some combinations of policy options are not supported.
• If MUST_CHANGE is specified, CHECK_EXPIRATION and CHECK_POLICY must be set to ON. Otherwise, the statement will fail.
• If CHECK_POLICY is set to OFF, CHECK_EXPIRATION cannot be set to ON. An ALTER LOGIN statement that has this combination of options will fail.

Important

CHECK_EXPIRATION and CHECK_POLICY are only enforced on Windows Server 2003 and later versions.
A known issue in Windows Server 2003 might prevent the bad password count from being reset after Lockout Threshold has been reached. This might cause an immediate lockout on subsequent failed login attempts. You can manually reset the bad password count by briefly setting CHECK_POLICY = OFF, followed by CHECK_POLICY = ON.

When SQL Server is running on Windows 2000, setting CHECK_POLICY = ON will prevent the creation of passwords that are:
• Null or empty
• Same as name of computer or login
• Any of the following: "password", "admin", "administrator", "sa", "sysadmin"

The security policy might be set in Windows, or might be received from the domain. To view the password policy on the computer, use the Local Security Policy MMC snap-in (secpol.msc).









Script:
USE [master]
GO
CREATE LOGIN [victor] WITH PASSWORD=N'winter123' MUST_CHANGE,
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON
GO

Strong Password

Passwords can be the weakest link in a server security deployment. You should always take great care when you select a password. A strong password has the following characteristics:

• Is at least 8 characters long.
• Combines letters, numbers, and symbol characters within the password.
• Is not found in a dictionary.
• Is not the name of a command.
• Is not the name of a person.
• Is not the name of a user.
• Is not the name of a computer.
• Is changed regularly.
• Is significantly different from previous passwords.

Microsoft SQL Server passwords can contain up to 128 characters, including letters, symbols, and digits. Because logins, user names, roles, and passwords are frequently used in Transact-SQL statements, certain symbols must be enclosed by double quotation marks (") or square brackets ([ ]). Use these delimiters in Transact-SQL statements when the SQL Server login, user, role, or password has the following characteristics:
• Contains or starts with a space character.
• Starts with the $ or @ character.
If used in an OLE DB or ODBC connection string, a login or password must not contain the following characters: [] {}(),;? * ! @. These characters are used to either initialize a connection or separate connection values.


Conclusion

The SQL Server Security offers many core tasks and options for Server, database, table...etc. It’s very quick and intuitive to use.

===================================================================

IMPORTANT SQL QUERY

2009-11-14T15:39:00.000-08:00

Important SQL Query



TRIGGER
1) Create a basic sql server 2005 trigger to send email alerts

http://blog.netnerds.net/2008/02/create-a-basic-sql-server-2005-trigger-to-send-e-mail-alerts/

CREATE TABLE dbo.inventory
(
item varchar(50),
price money
)
GO

CREATE TRIGGER expensiveInventoryMailer
ON dbo.inventory AFTER INSERT AS

DECLARE @price money
DECLARE @item varchar(50)

SET @price = (SELECT price FROM inserted)
SET @item = (SELECT item FROM inserted)

IF @price >= 1000
BEGIN
DECLARE @msg varchar(500)
SET @msg = 'Expensive item "' + @item + '" entered into inventory at $' + CAST(@price as varchar(10)) + '.'
--// CHANGE THE VALUE FOR @recipients
EXEC msdb.dbo.sp_send_dbmail @recipients=N'manager@domain.com', @body= @msg, @subject = 'SQL Server Trigger Mail', @profile_name = 'DBMailProfile'
END
GO

Backup Database
Description: Database backups one of the most important aspects SQL Server disaster recovery planning for any production system. Backups may be used to

provide a means of recovery to a point-in-time when the database was last operational.

Types of backups in SQL Server

1) Full Backup
2) Differential Backup
3) Transaction Log Backup
4) File(s) and Filegroup(s) backup


1) FULL Backup
Syntax
BACKUP DATABASE [Databasename]
TO DISK = [Path: Where you want to Store the Backup]
WITH INIT

Real Time
BACKUP DATABASE Employee
TO DISK = 'c:\backups\Employee.bak'
WITH INIT

2) Differential Backup
Syntax
BACKUP DATABASE {Database Name}
TO DISK = {Path: Where you want to store the Database}
WITH INIT, DIFFERENTIAL

Real Time
BACKUP DATABASE Employee
TO DISK = 'c:\backups\Employee_diff.bak'
WITH INIT, DIFFERENTIAL

3) Transaction Log Backup 
Syntax
BACKUP LOG {Database Name}
TO DISK = {Path: Where you want to store the Database}
WITH INIT

Real Time

BACKUP LOG Employee
TO DISK = 'c:\backups\Employee_log_1.bak'
WITH INIT

4) File(s) and Filegroup(s) backup

Syntax

BACKUP Database {databasename} FILE={filename},
FILEGROUP={filegroup} TO {device}

View SQL Server Agent job history
EXECUTE MSDB.DBO.SP_HELP_JOBHISTORY NULL, 'job name', @MODE = N'SEM'

To Find out the Drive Space

EXEC master.dbo.xp_fixeddrives

Result

Find out how many row are there in Employee database

SELECT tn.name as [TableNames] ,rc.rows as [TblRowCounts]

FROM sysobjects tn

INNER JOIN sysindexes rc ON (tn.id = rc.id)

WHERE tn.xtype = 'u'

AND rc.indid <> 

order by TableNames
 
 
Result
 

 
To find out All Database in the server
 
select name from master..sysdatabases select name from sys.databases
 
Result

 
Size of the database
 
select * from sysfiles
 
 
To Get the Particular Tables Size
 
EXEC sp_spaceused 'Employee.dbo.EmployeeInfo'
 
Result
 
 
SELECT c.name AS Sysadmin_Server_Role_Members FROM sys.server_principals a    INNER JOIN sys.server_role_members b  ON a.principal_id = b.role_principal_id  AND a.type = 'R' AND a.name ='sysadmin'   INNER JOIN sys.server_principals c     ON b.member_principal_id = c.principal_id
 
 


Query to Get List of System Tables

 select * from Sys.Objects where Type='s' 

Query to Get List of User Tables

 select * from Sys.Objects where Type='u' 

Query to Get List of Store Procedures

select * from Sys.Objects where Type='p'
select name,object_id,create_date from Sys.Objects where Type='p'
Query to Get List of Scalar Functions

select * from Sys.Objects where Type='fn'

select Name,object_ID,type_desc,create_date,modify_date from Sys.Objects where Type='fn' 
 

Query to Get List of Table Valued Functions
select * from Sys.Objects where Type='tf'
Query to Get List of Primary Keys 
select * from Sys.Objects where Type='PK'
select name,object_id,create_date,modify_date from Sys.Objects where Type='PK' 
 
Query to Get List of Unique Keys 
select * from Sys.Objects where Type='uq'

select name,object_id,create_date,modify_date from Sys.Objects where Type='uq' 

Query to Get List of Forgien Keys 
select * from Sys.Objects where Type='f'
select name,object_id,create_date,modify_date from Sys.Objects where Type='F' 

Query to Get List of views
select * from Sys.Objects where Type='v'
select name,object_id,create_date,modify_date from Sys.Objects where Type='V' 

Query to Get List of Triggers
select * from Sys.Objects where Type='tr' 

Query to Get List of Internal Tables
select * from Sys.Objects where Type='it'
select name,object_id,create_date,modify_date from Sys.Objects where Type='it' 

To get SQL server version
SELECT @@VERSION

To find all table names which do not occur in TSQL
code(views, SPs, functions), use this statement
select t.name, sys.objects.name foundin, sys.objects.type_desc
from sys.objects t
left outer join
sys.sql_modules
inner join sys.objects on sys.objects.object_id = sys.sql_modules.object_id
on sys.sql_modules.definition like '%' + t.name + '%'
where t.type = 'U'
and sys.objects.name is null
order by t.name, type_desc, foundin


Find out the Tables Modified in a SQL Server Database in the Last ‘N’ Days

USE Employee
GO
SELECT name AS [TableName],
SCHEMA_NAME(schema_id) AS [Schema],
modify_date as [ModifyDate]
FROM sys.objects
WHERE modify_date > GETDATE() - 15 -- 'N' days
and type_desc = 'USER_TABLE'
order by ModifyDate desc -- Descending order
-- order by ModifyDate asc -- Descending order
GO

Find Partition Range in a SQL Server 2005/2008 PartitionTable

SELECT psch.name as PartitionScheme,
prng.value AS ParitionValue,
prng.boundary_id AS BoundaryID
FROM sys.partition_functions AS pfun
INNER JOIN sys.partition_schemes psch ON pfun.function_id = psch.function_id
INNER JOIN sys.partition_range_values prng ON prng.function_id=pfun.function_id
WHERE pfun.name = 'TransactionRangePF1'

Find Partition Information in a SQL Server 2005/2008 Table using T-SQL

SELECT DISTINCT part.object_id as ObjectID,
OBJECT_NAME(part.object_id) as TableNm,
idx.name as IndexNm,
idx.type_desc as IndexType,
psch.name as PartitionScheme,
pfun.name as FunctionName
FROM sys.partitions part
INNER JOIN sys.indexes idx
ON part.[object_id] = idx.[object_id] and part.index_id = idx.index_id
INNER JOIN sys.data_spaces dsp
ON idx.data_space_id = dsp.data_space_id
INNER JOIN sys.partition_schemes psch
ON dsp.data_space_id = psch.data_space_id
INNER JOIN sys.partition_functions pfun
ON psch.function_id = pfun.function_id

Find Constraints of All Tables in a SQL Server 2005/2008 Database

USE Employee
GO
SELECT OBJECT_NAME(object_id) as [Constraint],
OBJECT_NAME(parent_object_id) AS [Table],
type_desc AS [Constraint Type],
create_date AS [Creation Date]
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
ORDER BY [Table]
GO

Display List of All Databases with their Sizes in SQL Server 2005/2008

SELECT sysDa.Name,sysDa.create_date,sysDa.recovery_model_desc, temp.DBSize8KBPage
FROM
(
SELECT sysMas.database_ID, sysMas.size, SUM(size)  as DBSize8KBPage
FROM sys.master_Files sysMas
GROUP BY sysMas.DataBase_ID, sysMas.size
)
temp
INNER JOIN
Sys.Databases sysDa
on temp.Database_ID = sysDa.DataBase_ID

List down the DataType and Size Of all Columns of
All Tables in a SQL Server 2005/2008 Database

USE Employee;
GO
SELECT
OBJECT_NAME(col.OBJECT_ID) as [TableName],
col.name AS [ColName],
SCHEMA_NAME(typ.schema_id) AS Type_schema,
typ.name AS Type_name,
col.Max_length
FROM sys.columns AS col
JOIN sys.tables AS tbl on col.object_id =  tbl.object_id
JOIN sys.types AS typ ON col.user_type_id = typ.user_type_id
--ORDER BY [TableName];
ORDER BY [ColName]
GO

Creating a Self-Signed Certificate in SQL Server 2005/2008

USE Employee;
CREATE CERTIFICATE SocialCert
ENCRYPTION BY PASSWORD = 'q35RED6gg577gghy53FF'
WITH SUBJECT = 'ID CERTIFICATE',
EXPIRY_DATE = '12/31/2010';
GO

SQL Web Sites

2009-10-07T14:49:00.000-07:00

SQL Server Important Web SITE

SQL Server 2005 Architecture

http://www.mssqltips.com/categorylist.asp?ns

http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx

http://www.programmersheaven.com/2/SQL-server-2005-school

http://www.programmersheaven.com/2/SQL-server-2005-school-lesson-2

Mail Send Related

http://www.databasejournal.com/features/mssql/article.php/3626056/Database-Mail-in-SQL-Server-2005.htm

http://www.sql-server-performance.com/articles/dba/email_functionality_p1.aspx

http://social.msdn.microsoft.com/forums/en-US/sqltools/thread/81b8a864-6823-41ec-8166-e1b7537c3867/

http://articles.techrepublic.com.com/5100-10878_11-6164310.html?tag=leftCol;post-248

http://articles.techrepublic.com.com/5100-10878_11-5094201.html

http://articles.techrepublic.com.com/5100-10878_11-6161839.html

http://www.datasprings.com/Resources/ArticlesInformation/CreatingEmailTriggersinSQLServer2005.aspx

Naming Standards Related Web Sites

http://technet.microsoft.com/en-us/library/ms124438.aspx

http://www.cms.hhs.gov/dbadmin/downloads/sqlserverstandardsandguildelines.pdf

http://www.arc.sci.eg/NARIMS_upload/NARIMSdocs/73391/Appendix%20A_%20DatabaseObjectNamingStandardsSQLServer.pdf

http://blogs.msdn.com/steven_bates/archive/2006/03/20/Database-Object-Naming-Rules.aspx

http://blog.sqlauthority.com/2008/09/25/sql-server-guidelines-and-coding-standards/

SQL Security & User & Login Rights Related Web Sites

http://msdn.microsoft.com/en-us/library/bb510589.aspx

http://msdn.microsoft.com/en-us/library/bb283235.aspx

http://msdn.microsoft.com/en-us/library/aa337562.aspx

http://blog.sqlauthority.com/2009/04/28/sql-server-introduction-to-sql-server-encryption-and-symmetric-key-encryption-tutorial-with-script/

http://www.databasejournal.com/features/mssql/article.php/3357861/SQL-Server-2000-Security---Part-4-Authorization.htm

http://anasoft.wordpress.com/2007/06/17/sql-server-2005-security-architecture/

Disaster Recovery

http://support.microsoft.com/kb/822400

http://technet.microsoft.com/en-us/library/cc917680.aspx

http://www.mssqltips.com/tip.asp?tip=1595

http://www.exforsys.com/tutorials/sql-server-2005/sql-server-disaster-recovery.html

Installation of SQL Server 2005 Related Web Sites

http://www.exforsys.com/tutorials/sql-server-2005/getting-started-with-sql-server-2005.html

Log Shipping Related Web Sites

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1309728,00.html

http://www.informit.com/articles/article.aspx?p=331402&seqNum=4

http://www.mssqltips.com/tip.asp?tip=1158

Maintenance Plan

http://msdn.microsoft.com/en-us/library/ms189036(SQL.90).aspx

http://cherupally.blogspot.com/2009/04/schedule-daily-backup-for-sql-server_27.html

http://www.sql-server-performance.com/articles/dba/creating_backup_jobs_p1.aspx

http://www.mssqltips.com/tip.asp?tip=1094

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1168448,00.html

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1225990_mem1,00.html

http://blogs.techrepublic.com.com/datacenter/?p=248

http://www.mssqltips.com/tip.asp?tip=1016

http://www.exforsys.com/tutorials/sql-server-2005/sql-server-2005-maintenance-plan-using-wizard.html

SSIS

http://www.accelebrate.com/sql_training/ssis_tutorial.htm

http://www.sql-server-performance.com/articles/dba/package_configuration_2005_p1.aspx

Data modeling

http://www.learndatamodeling.com/

http://www.datamodeling.info/index.jsp

Excel to SQL Server 2005

http://blogs.techrepublic.com.com/datacenter/?p=205

Backup and Restore

http://articles.techrepublic.com.com/5100-10878_11-1041267.html

SQL FAQ 2

2009-07-24T18:25:00.000-07:00

SQL FAQ

Display all tables name in SQL Server?
Select * from sysobjects where xtype='U'

Single Query Update salary with condition
Update Employee SET salary = CASE
WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000
WHEN salary BETWEEN 40001 AND 50000 THEN salary + 7000
WHEN salary BETWEEN 50001 AND 60000 THEN salary + 9000
END

Which TCP/IP port does the SQl Server is running?

SQL Server runs on port 1433. It can be change from the Network Utility TCP/IP then enter into Properties à Port Number. Both on client and the Server.

What’s the maximum size of a row?

8060 bytes. Don’t be surprised with questions like ‘what is the maximum number of columns per table’. 1024 columns per table. Check out SQL Server books online for the page titled: "Maximum Capacity Specifications".

Find the Duplicate Values in a table
SELECT EmpID from Employee a where
(SELECT count(*) from Employee b where
a.EmpID=b.EmpID) > 1

Delete duplicate Record
WITH deldup as (Select row_number() over
(partition by EmpID order by EmpID) as RONO,
EmpID from Employee)
Delete from deldup where RONO>1

How to Findout Nth highest Salary
Select salary from Employee A
where n=(select count(distinct salary) from
Employee B where A.salary <= B.salary

Example for Group by Having
Select Depart,Sum(salary) from Employee group by
Depart having sum(salary) >= 1000

To copy the whole tables with data
Select * into [New Table] from [Old Table]
Select * into EmployeeNew from Employee

Copy structure only
Create table [New Table] as select * from [Old table] where 1=2
Create table EmployeeNew as select * from Employee where 1=2

To find total row in a table
USE [Database name] EXEC sp_spaceused [Table Name]
USE Employee EXEC sp_spaceused Department

How Rename the table
USE [Database Name] EXEC sp_rename [Old Table Name], [New Table Name]
USE Employee EXEC sp_rename Depart, DepartNew

How to add new Data type for Database
USE Employee EXEC sp_addtype USPhoneNumber, 'char(12)', 'NOT NULL'

To find out the login that is mapped to the built-in “dbo” user
USE Employee
GO
SELECT b.name AS Login_Mapped_to_DBO
FROM sys.database_principals a
INNER JOIN sys.server_principals b
ON a.sid = b.sid
WHERE a.name = 'dbo'

To find out old db_owner role
USE Employee
GO
SELECT c.name AS DB_Owner_Role_Member
FROM sys.database_principals a
INNER JOIN sys.database_role_members b
ON a.principal_id = b.role_principal_id AND a.is_fixed_role = 1
AND a.name ='db_owner'
INNER JOIN sys.database_principals c
ON b.member_principal_id = c.principal_id

To get the NULL passwords Username
Use master Select name,Password
from syslogins
where password is null
order by name

To Find out the User tables in a Database
SELECT * from sysobjects where xtype = 'U'
SELECT * from sysobjects where xtype = 'U' and name like 'd%'
SELECT * from sysobjects where name like 'd%'

To Get The Column Name,DataType, And Length of columns
select column_name, data_type, character_maximum_length
from information_schema.columns