Thursday 1 September 2011

Copying And Moving SQL Server Logins

----------- 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

Posted by at No comments:

BACKUP Details




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
Posted by at No comments:

Collation


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




Posted by at No comments:

Comma Seperater


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




Posted by at No comments:

All about space Details


--- 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]
Posted by at No comments:

SQL Server Service Startup Time

--- To Check when Sql service is started

use master

SELECT login_time as ServiceStartUpTIme FROM sysprocesses WHERE spid = 1

go

---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




Posted by at No comments:
Subscribe to: Posts (Atom)