I’m NOT responsible for any damage that occurs doing so, USE AT YOUR OWN RISK! … If anything goes wrong. Read carefully, i am sure 99.99 % all syntax work.  @addarr.

What does the sp_help_revlogin do?
The sp_help_revlogin SP is a Microsoft provided utility that generates a TSQL script to migrate logins from one server to another. This SP will not only copy the existing logins, but it will also copy the passwords and Security Identification Numbers (SID) associated with SQL Server Authenticated users.

When you move a database from one server to another, the entire database and all the system tables associated with it are also moved. One of those system tables is the sysusers table. The sysusers table contains all the users, groups and roles that have access to the database. In order for a person to be able to access the database, they must have two things. The first thing they must have is a SQL Server login. The second thing they need is to be defined as a user in the database. Therefore, if you copy a database from one server to another and the users of the database don’t have SQL Server logins then these database users become orphan users. An orphan user is a user in a database with a SID that does not exist in the syslogins table in the master database. Also if the SID stored in the database sysusers table, differs from SID stored in the syslogin table for the matching database user, then the database user is also considered an orphan user. If you retain the original SID for logins, when a user database is migrated, you will not have a problem with orphan users.

/*Copy users in sql server 2005, 2008 & 2012.  
This is code to copy sql and windows users from one sql 2005, 2008 & 2012 instance to another:*/

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
EXEC sp_help_revlogin
/* Remarks
Review the following information before you run the output script on the instance on server B:•
Review the output script carefully. If server A and server B are in different domains, you have to modify the output script.
Then, you have to replace the original domain name with the new domain name in the CREATE LOGIN statements. */

Page split:

When you think of page splits, you normally only think of clustered indexes. This is because clustered indexes enforce the physical order of the index, and page splitting can be a problem if the clustered index is based on a non-incrementing column. But what has this to do with non-clustered indexes?
While non-clustered indexes use a clustered index (assuming the table is not a heap) as their key, most people don’t realize that non-clustered indexes can suffer from page splitting, and because of this, need to have an appropriate fillfactor and pad_index set for them.

Here’s an example of how non-clustered indexes can experience page splits. Let’s say you have a table that has a clustered index on it, such as customer number. Let’s also say that you have a non-clustered index on the zip code column. As you can quite well imagine, the data in the zip code column will have no relation to the customer number and will be more or less random, and data will have to be inserted into the zip code index randomly. Like clustered index pages, non-clustered index pages can experience page splitting.

So just as with clustered indexes, non-clustered indexes need to have an appropriate fillfactor and pad_index, and also be rebuild on a periodic basis.

Non-clustered indexes are best for queries: 

That return few rows (including just one row) and where the index has good selectivity (generally above 95%). That retrieve small ranges of data (not large ranges). Clustered indexes perform better for large range queries.

Where both the WHERE clause and the ORDER BY clause are both specified for the same column in a query. This way, the non-clustered index pulls double duty. It helps to speed up accessing the records, and it also speeds up the sorting of the records (because the returned data is already sorted).

That use JOINs (although clustered indexes are better).

When the column or columns to be indexed are very wide. While wide indexes are never a good thing, but if you have no choice, a non-clustered index will have overall less overhead than clustered index on a wide index.

Maxdop:  On multiprocessor servers it is common with paralelism problems on large joins or heavy queries . It can give better perfomance to add maxdop. e.g.

select * from databases option (maxdop 1)

The number(1) states how many processors we want to be used as a maximum for this query. As a rule of thumb, never use more processors than you have physical processors in the server. Each query needs to be tested to find out how many processors to use for best performance. Max degree of paralelism can also be set for the whole server via sp_configure (max degree of parallelism ) This will affect all queries without the maxdop option.

Re-start server remotely: 

Very often RDP is failing on the nodes in our cluster when we are restarting the server or something else is happenign that requires an restart. To restart a server we can use one very simple command from any other server. The only requirement is that we have restart rights on the other server and that we are logged in to any server with that account. För example we are logged in to addarr-sec and need to restart addarr1-pri

Open a commando prompt – cmd and type in:

shutdown /i

This will start up the normal shutdown dialogue. Add the your servername you want to restart or shutdown and then do as usual.  

1. Code to start job
This code can start a job from any other sql server ( both 2000 and 2005) i have been using it from UTS to let the customer start their own SSIS package.

In 2005 we can let a user have start rights on jobs without being sys user. Ny putting the job in an 2005 instance i cna let the customer starts job that runs agains a sql 2000 database(UTS). In this code i use a user and password but i can also let them use intergated security and that is an better solution for security reasons.

declare @retcode int
declare @job_name varchar(300)
declare @server_name varchar(200)
declare @query varchar(8000)
declare @cmd varchar(8000)
set @job_name = ‘Copy UTS to Mindtree’set @server_name = ’122.00.143.000,1304′
set @query = ‘exec msdb.dbo.sp_start_job @job_name = ”’ + @job_name + ””
set @cmd = ‘osql -U uts -Putsmind -S ‘ + @server_name + ‘ -Q “‘ + @query + ‘”‘
exec @retcode = master.dbo.xp_cmdshell @cmd
if @retcode <> 0 or @retcode is null
begin print ‘xp_cmdshell @retcode = ‘+isnull(convert(varchar(20),@retcode),’NULL @retcode’)
end

2. This code can be used to identify all indexed that never have been used.
This lists all indexes that have not been used since the last start up of sql server 2005 instance. The index have bene updated (caused IO and CPU) but never referenced, used, scaned, seeked in etc. Note, before you delete any index, always run this for a long time. It might be that the code using the index just runs once every 3 month or so! This gives us a very good viwe of how the indexes is not used anyway.

select object_name(i.object_id),
i.name,
s.user_updates,
s.user_seeks,
s.user_scans,
s.user_lookups
from sys.indexes i left join sys.dm_db_index_usage_stats s
on s.object_id = i.object_id and i.index_id = s.index_id and s.database_id = 5
where objectproperty(i.object_id, ‘IsIndexable’) = 1 and
– index_usage_stats has no reference to this index (not being used)
s.index_id is null or
– index is being updated, but not used by seeks/scans/lookups
(s.user_updates > 0 and s.user_seeks = 0
and s.user_scans = 0 and s.user_lookups = 0)
order by object_name(i.object_id) asc

3. Code to list changes of tables, views, indexes etc in the last so many days  Change the number 20 to the days back in time you what to check. Works with sql server 2005 only.

SELECT name AS object_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM sys.objects
WHERE modify_date > GETDATE() – 20
ORDER BY modify_date;

For sql server 2000:

SELECT name AS object_name

,type
,crdate as Create_date
,refdate as ChangeDate
FROM sysobjects
WHERE refdate > GETDATE() – 20
ORDER BY ChangeDate;

List all tables without any sort of index at all
create table #Ttable (Table_name varchar(100) collate latin1_general_CI_AS not null)
insert into #Ttable
SELECT t.name AS table_name
FROM sys.tables AS t
WHERE object_id IN
(
SELECT object_id
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,’IsIndexed’) = 0
)
ORDER BY table_name;
select Table_name,i.rows
from sysindexes i, sysobjects o, #Ttable t
where i.id = o.id and t.table_name = o.name collate latin1_general_CI_AS
and i.indid <= 1
and o.type = ‘U’
order by i.rows desc
drop table #Ttable

Code to list all tables without primarykey and the number of rows in sql 2005
create table #Ttable (Table_name varchar(100) collate latin1_general_CI_AS not null)
insert into #Ttable
SELECT t.name AS table_name
FROM sys.tables AS t
WHERE object_id NOT IN
(
SELECT parent_object_id
FROM sys.key_constraints
WHERE type_desc = ‘PRIMARY_KEY_CONSTRAINT’ — or type = ‘PK’
)
ORDER BY table_name;
select Table_name,i.rows
from sysindexes i, sysobjects o, #Ttable t
where i.id = o.id and t.table_name = o.name collate latin1_general_CI_AS
and i.indid <= 1
and o.type = ‘U’
order by i.rows desc
drop table #Ttable

4. Code to get tables without any Clustered indexes and the number of rows in the tables.  This code is for sql 2005:

create table #Ttable (Table_name varchar(100) collate latin1_general_CI_AS not null)
insert into #Ttable
SELECT t.name AS table_name
FROM sys.tables AS t
WHERE NOT EXISTS
(
SELECT * FROM sys.indexes AS i
WHERE i.object_id = t.object_id
AND i.type = 1 — or type_desc = ‘CLUSTERED’
)
ORDER BY table_name;
select Table_name,i.rows
from sysindexes i, sysobjects o, #Ttable t
where i.id = o.id and t.table_name = o.name collate latin1_general_CI_AS
and i.indid <= 1
and o.type = ‘U’
order by i.rows desc
drop table #Ttable

Code to copy sql 2000 users to sql 2005

This code  is for copying logins from sql 2000 and create them on an sql 2005 instance:

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(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
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_2000_to_2005′) IS NOT NULL
DROP PROCEDURE sp_help_revlogin_2000_to_2005
GO
CREATE PROCEDURE sp_help_revlogin_2000_to_2005

@login_name sysname = NULL,
@include_db bit = 0,
@include_role bit = 0

AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @dfltdb varchar (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)

IF (@login_name IS NULL)
DECLARE login_curs CURSOR STATIC FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), ‘master’)
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] ’sa’
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), ‘master’)
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] = @login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb

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 ”
PRINT ”
PRINT ”
PRINT ‘/***** CREATE LOGINS *****/’

WHILE @@fetch_status = 0
BEGIN
PRINT ”
SET @tmpstr = ‘– Login: ‘ + @name
PRINT @tmpstr

IF (@xstatus & 4) = 4
BEGIN — NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN — NT login is denied access
SET @tmpstr = ” –’EXEC master..sp_denylogin ”’ + @name + ””
PRINT @tmpstr
END
ELSE
BEGIN — NT login has access
SET @tmpstr = ‘IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ”’ + @name + ”’)’
PRINT @tmpstr
SET @tmpstr = CHAR(9) + ‘CREATE LOGIN [' + @name + '] FROM WINDOWS’
PRINT @tmpstr
END
END
ELSE
BEGIN — SQL Server authentication
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT

IF (@binpwd IS NOT NULL)
BEGIN — Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
SET @tmpstr = ‘CREATE LOGIN [' + @name + '] WITH PASSWORD=’ + @txtpwd + ‘ HASHED’
END
ELSE
BEGIN — Null password
SET @tmpstr = ‘CREATE LOGIN [' + @name + '] WITH PASSWORD=””’
END

SET @tmpstr = @tmpstr + ‘, CHECK_POLICY=OFF, SID=’ + @SID_string
PRINT @tmpstr
END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END

IF @include_db = 1
BEGIN
PRINT ”
PRINT ”
PRINT ”
PRINT ‘/***** SET DEFAULT DATABASES *****/’

FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb

WHILE @@fetch_status = 0
BEGIN
PRINT ”
SET @tmpstr = ‘– Login: ‘ + @name
PRINT @tmpstr

SET @tmpstr = ‘ALTER LOGIN [' + @name + '] WITH DEFAULT_DATABASE=[' + @dfltdb + ']‘
PRINT @tmpstr

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
END

IF @include_role = 1
BEGIN
PRINT ”
PRINT ”
PRINT ”
PRINT ‘/***** SET SERVER ROLES *****/’

FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb

WHILE @@fetch_status = 0
BEGIN
PRINT ”
SET @tmpstr = ‘– Login: ‘ + @name
PRINT @tmpstr

IF @xstatus &16 = 16 — sysadmin
BEGIN
SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=’’sysadmin”’
PRINT @tmpstr
END

IF @xstatus &32 = 32 — securityadmin
BEGIN
SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=’’securityadmin”’
PRINT @tmpstr
END

IF @xstatus &64 = 64 — serveradmin
BEGIN
SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=’’serveradmin”’
PRINT @tmpstr
END

IF @xstatus &128 = 128 — setupadmin
BEGIN
SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=’’setupadmin”’
PRINT @tmpstr
END

IF @xstatus &256 = 256 –processadmin
BEGIN
SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=”processadmin”’
PRINT @tmpstr
END

IF @xstatus &512 = 512 — diskadmin
BEGIN
SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=”diskadmin”’
PRINT @tmpstr
END

IF @xstatus &1024 = 1024 — dbcreator
BEGIN
SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=”dbcreator”’
PRINT @tmpstr
END

IF @xstatus &4096 = 4096 — bulkadmin
BEGIN
SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=”bulkadmin”’
PRINT @tmpstr
END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
END

CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1
GO

Fix code for suspect database:  If a databse is in suspect code, this code can correct it. Note after you have run ALTER DATABASE LDS_CN002010 SET EMERGENCY, run each of the following rows one by one. If checkdb is reproitng errros you migh try with the the DBCC CheckDB (‘LDS_CN002010′, REPAIR_ALLOW_DATA_LOSS)

Note!!! That will realy remove any pages, linsk etc that are corupted and remove that data that where there. This will for sure lead to missing data. Ins ome cases this is however the only choice. If there is an backup it is a better choice to use that in the first place.

EXEC sp_resetstatus ‘LDS_CN002010′;
ALTER DATABASE LDS_CN002010 SET EMERGENCY
DBCC checkdb(‘LDS_CN002010′)
ALTER DATABASE LDS_CN002010 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (‘LDS_CN002010′, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE LDS_CN002010 SET MULTI_USER
ALTER DATABASE LDS_CN002010 SET online

How to turn of off all contraints in code :

Very often when we migrate data, like with Big and the NY-USA incident, we need to be bale to turn off all check contraints to be able to truncate data in tables. To do this we can use the gui and that takes a lot of time. A better way is to use this code:

– To turn off all check contrains(e.g forreign keys)
ALTER TABLE VDS_VehicleMaster NOCHECK CONSTRAINT ALL
– Enable all table constraints
ALTER TABLE VDS_VehicleMaster CHECK CONSTRAINT ALL

Note, in many cases you also need to do the same on the other tables involved in the realtionship. This saved me hours when migrating the data.

From Mehboob SQLDBA – MCITP  www.addarr.com