Modifying MSDB system procedures

I personally do not recommend this on production environment but have to do myself to fix the problem of not allowing some users to have sysadmin rights for the sake of creating packages and dropping packages. After a few investigations, I have come up with this idea of what if modifying some of the stored procedures in msdb database that is used to add packages.

I needed to change only two of the system-stored procedures. The two system stored procedures that are modified and the lines commented out are shown below. It just prevents the stored procedure not to check if a user is a member of sysadmin or owner of the package.
1 . sp_add_dtspackage

Commented script
/*
ELSE
BEGIN
--// Only the owner of DTS Package ''%s'' or a member of the sysadmin role may create new versions of it.
IF (@owner_sid <> SUSER_SID() AND (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1))
BEGIN
RAISERROR (14586, -1, -1, @name)
RETURN(1) -- Failure
END
END
*/

2. sp_drop_dtspackage
/*
IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)
BEGIN
IF (NOT EXISTS (SELECT * FROM sysdtspackages WHERE id = @id AND owner_sid = SUSER_SID()))
BEGIN
SELECT @name = name FROM sysdtspackages WHERE id = @id
RAISERROR (14587, -1, -1, @name)
RETURN(1) -- Failure
END
END
*/

Once all the changes are saved to msdb database, I have given exec permission to the above stored procedures and it did at least served the purpose. Now, I don’t have to give sysadmin permissions to a user/developer who just need to change the dts package.

Jobs running under

The following sql will help you to find out SQL job name and owner of the job.

select sl.name, j.name from sysjobs j
join master.dbo.syslogins sl
on sl.sid = j.owner_sid

Random password Generator

The following has helped me in generating random and complex passwords. I want to share with you and if you have any comments and would like to improve on it, you are welcome.

declare @type tinyint, @Length tinyint
DECLARE @password varchar(250)
set @password = ''
set @Length = 250
while @Length > 0
BEGIN
SET @type = ROUND(1 + (RAND() * (3)),0)
IF @type = 1
SET @password = @password + CHAR(ROUND(97 + (RAND() * (25)),0))
ELSE IF @type = 2
SET @password = @password + CHAR(ROUND(65 + (RAND() * (25)),0))
ELSE IF @type = 3
SET @password = @password + CHAR(ROUND(48 + (RAND() * (9)),0))
ELSE IF @type = 4
SET @password = @password + CHAR(ROUND(33 + (RAND() * (13)),0))
SET @Length = @Length - 1
END
SELECT @password As ComplexPassword

Tables with No index

Similar to my previous post but gets tables with no index on them.

SELECT distinct o.name from sysobjects o
left outer join (select distinct object_name(id) as TableName from sysindexes where indid between 1 and 249) t
on t.TableName = o.name
where o.xtype = 'u' and t.TableName is null

Tables with no Clustered Index

Have used the following to see tables in database without clustered index.
SELECT o.name from sysobjects o
left outer join (select distinct object_name(id) as TableName from sysindexes where indid = 1) t
on t.TableName = o.name
where o.xtype = 'u' and t.TableName is null

User name, group name and thier default database

The following script will help you to list all user name, group name and thier default database. The script will help you to list all of the above on single instance but includes all databases. I use this script as starting point to fix any security issues.


set nocount on
declare @dbName sysname, -- database name
@dbid int -- database Id

IF (object_id('tempdb..#userDetails') IS not Null)
Drop Table #userDetails
-- create temp table to hold info
BEGIN
CREATE TABLE #userDetails
(DbName sysname,
UserName sysname,
GroupName sysname,
LoginName sysname,
UserDefaultDB sysname)
END

declare @dbnames table(dbid int not null primary key clustered, dbname nvarchar(100))
INSERT INTO @dbnames(dbid, dbname)
select dbid, name from master.dbo.sysdatabases where dbid > 4 and name not like '%Sharepoint%'
select @dbid = max(dbid) from @dbnames

while @dbid is not null
begin
SELECT @dbName = dbname FROM @dbnames
WHERE dbid = @dbid
EXECUTE(
'use ' + @dbName + '
INSERT INTO #userDetails(DbName, UserName, GroupName, LoginName, UserDefaultDB)
SELECT db_name() as DBName, usu.name As UserName , case when (usg.uid is null) then ''public'' else usg.name end as GroupName ,
lo.loginname ,lo.dbname as UserDefaultDbName
from sysusers usu
join
(sysmembers mem inner join sysusers usg on mem.groupuid = usg.uid) on usu.uid = mem.memberuid
join master.dbo.syslogins lo on usu.sid = lo.sid
where (usu.islogin = 1 and usu.isaliased = 0 and usu.hasdbaccess = 1)
and (usg.issqlrole = 1 or usg.uid is null)
')

select @dbid = max(dbid) from @dbnames
where dbid < @dbid end select * from #userDetails order by DbName, UserName, GroupName asc

connection not closed or Long running Queries

The following code will help to find the spid's that are sitting on your server for long time. This could happen when a connection is not closed or when the queries you are running is taking long time.

SELECT spid, cmd, status, loginame, open_tran, datediff(s, last_batch, getdate ()) AS [WaitTime(s)]FROM master..sysprocesses pWHERE open_tran > 0AND spid > 50AND datediff (s, last_batch, getdate ()) > 1000ANd EXISTS (SELECT * FROM master..syslockinfo l WHERE req_spid = p.spid AND rsc_type <> 2)

Get all triggers in an instance

The following code will help you to list all triggers in an instance.

declare @dbs table(databaseId int identity(1,1) not null primary key clustered, DatabaseName nvarchar(100))
create table #TriggersinDbs
(DatabaseName nvarchar(100),
ParentObject nvarchar(300),
TriggerName nvarchar(300)
)
INSERT INTO @dbs(DatabaseName)
SELECT name from master.dbo.sysdatabases where dbid > 4

declare @dbname nvarchar(100), @counter int, @objecttype nvarchar(3)

select @counter = max(databaseId) from @dbs
SET @objecttype = 'TR'
while @counter is not null
begin
select @dbname = DatabaseName from @dbs where databaseId = @counter

exec ( ' use ' + @dbname + '
INSERT INTO #TriggersinDbs(DatabaseName, ParentObject, TriggerName)
select '''+ @dbname + ''' as DatabaseName, object_name(parent_obj) as Parent_object,
name as TriggerName from sysobjects where xtype = '''+ @objecttype +'''')

select @counter = max(databaseId) from @dbs
where databaseId < @counter end SELECT * FROM #TriggersinDbs ORDER BY DatabaseName, ParentObject, TriggerName ASC drop table #TriggersinDbs

buffer size

***** Data for source column is too large for the specified buffer size while trying to import data in excel spreadsheet with a bulk text in it. *****
Some of you might have come accross this problem. There are two ways you can work around this problem.

1. The first and easy way is to Save the excel file as a .txt or .csv file and then import.This will work in most cases. If you have problem with this one you can play with registery at your own risk. But I have used in many times and it doesn't harm your system.

2. Play with registery:
Go to
- HKEY_LOCAL-MACHINE
- software
- Microsoft
- JET
- 4.0
- Engines
- Excel
- TypeGuessRows to the number of rows in your excel table. By default this value is 8.

SQL Server references 1

This is my first draft of the lists that I want to build to help SQL Server professionals as well as beginners who want to get resources in one place. Please feel free to send me your comments and urls that you feel is very important to be added to the list.

SQL Server 2005 home page

SQL Server related web casts

SQL Server 2005 Virtual Labs

SQL Server 2005 certification

SQL Server 2000 Resource Kit

Clustering 1

Internal and External Fragmentation

User database admin

Cost of Database Fragmentation

SQL server performance articles on sql-server-performance.com

SQL Server 2000 backup and restore

Move database from one server to another

SQL Server scripts

Blocking resolution

SQLIO Disk Subsystem Benchmark Tool

SQL Server Health and History Tool (SQLH2) Performance Collector

Best Practices Analyzer Tool for Microsoft SQL Server 2000 1.0

SQL Server Health and History Tool (SQLH2)

SQL Server Web Data Administrator

Tables with computed columns

Computed columns some times causes re-indexing of database to fail. To find out which tables got computed columns, I normally use the following query.

select so.id as ObjectId,
so.name as ObjectName,
sc.name as ComputedColumnName
FROM sysobjects so, syscolumns sc
where so.id = sc.id
and sc.iscomputed = 1
and so.xtype = 'u'

Number of Triggers

This script is used to find the number of triggers on database tables in database.

select object_name(parent_obj) as ObjectName,
count(name) as TotalTriggers
from sysobjects where xtype = 'tr'group by object_name(parent_obj)order by count(name) desc

Tables no primary key

Have you ever wondered how to find tables without primary key ? You can use the following statments to find one
use pubs
go
select o.Table_Name
FROM
(select name as Table_Name from sysobjects where xtype = 'U') o
LEFT OUTER JOIN
(
SELECT Table_Name FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE = 'PRIMARY KEY') pk
on pk.Table_Name = o.Table_Name
WHERE pk.Table_Name is null

Replication error

You may get the following error on distribution agent, Cannot insert duplicate key row in object 'xxxx' with unique index 'yyyy'.(Source: ServerName(Data source); Error number: 2601). Why this has happend and how to resolve this ?

The casue of the above error could be new row has been added at the publisher, but a row with the same key already exists at subscriber. When the distribution agent runs and tries to insert the new row at the subscriber it fails because a row with the same unique key already exists.

To resolve this issue do the following,
Identify the row at the subscriber with the same unique key as the one in your distribution agent log,delete identified rows at the subscriber and restart the publication agent for that subscriber

The above steps should fix the problem.

Enable replication agents for logging to output

To find out how to enable replication agents for logging to output files in SQL Server Enable replication agents for logging output.
The following options can be used in replication agents to enable logging to an output file:
-Output C:\ReplOutput.txt -OutputVerboseLevel [0|1|2]

VerboseLevel 0 - prints only the error messages
VerboseLevel 1 - Prints all the progress report messages
VerboseLevel 2 - is the defualt on and it prints both messages for level 0 and 1.

DTSRun command line decryption utility

Have you ever had problem identifying what the package name of DTSRun /~Z0xF2E216E36948A6C83A….. . I had recently had problem identifying what package a schedule task is running. When you use Wizard to schedule a task, it actually encrypts. I came across this useful tool that will decrypt and tell you the package name, server and etc.

You can download the tool (DTSRUNDEC) to decrypt encrypted values. http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26

Steps that you need to follow:
1. Download the DTSRUNDEC tool
2. Get the exe and put on c:3. Copy the encrypted values from Z onwards
4. C:\DTSRUNDEC

This will give you detailed description of the package.

sp_helpDB give you error

Some times when you try to use sp_helpDB you may get the following problem. It can result in your database even not backuped up.

Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails.

This error might be because your database have an invalid owner. You can identify the databases with this issue using the followng script.
use master
go
SELECT name, SUSER_SNAME(sid) FROM sysdatabases WHERE SUSER_SNAME(sid) IS NULL.

The above statement will produce databases with invalid database owner. You can fix this by running the following system stored procedure. To fix this problem run sp_changedbowner 'sa'. You can replace sa with a vaild user.

distribution database corrupted

If you have got a corrupt distribution database and unable to remove or configure your publication then follow the following steps.

1. SELECT * from msdb..msdistributiondbs
2. If a row is returned delete it.
3. Then, reconfigure replication.

What if the above steps didn't work, the one option I think of is to remove the replication from the server. If you have any other way , please let me know.

use master
go
sp_configure 'allow update', 1
go
reconfigure with override
go
DELETE master..sysservers WHERE srvname = 'repl_distributor'

go
sp_configure 'allow update', 0
go
reconfigure with override
go

SQL Server Certifications

Certifications for professional developers and database professionals have been redesigned.

If you want to get certified, find details of new route on Microsoft site New Certifications

sp_who2 to sp_whoDB

As a DBA, there might not be a date that you didn’t use this tool. It is a wonderful tool and you will also be asked on attending interviews. But, have you wondered of adding parameters to this procedure and use it only to show only information on specific database? Well, a small modification of this stored procedure helped me in doing this, I am sure it will help you too. There isn’t much modification to it but the idea of only seeing the connection to database that you want is in my opinion is good one. You can create this sp in master database and use the same as you use sp_who2. The only difference is you can pass the database name as a parameter.



CREATE PROCEDURE sp_whoDB
@dbname sysname = null,
@loginame sysname = NULL
as

set nocount on
declare
@retcode int
declare @dbid int

select @dbid = dbid from sysdatabases where name = @dbname

declare
@sidlow varbinary(85)
,@sidhigh varbinary(85)
,@sid1 varbinary(85)
,@spidlow int
,@spidhigh int

declare
@charMaxLenLoginName varchar(6)
,@charMaxLenDBName varchar(6)
,@charMaxLenCPUTime varchar(10)
,@charMaxLenDiskIO varchar(10)
,@charMaxLenHostName varchar(10)
,@charMaxLenProgramName varchar(10)
,@charMaxLenLastBatch varchar(10)
,@charMaxLenCommand varchar(10)

declare
@charsidlow varchar(85)
,@charsidhigh varchar(85)
,@charspidlow varchar(11)
,@charspidhigh varchar(11)
--------
select
@retcode = 0 -- 0=good ,1=bad.

--------defaults
select @sidlow = convert(varbinary(85), (replicate(char(0), 85)))
select @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))

select
@spidlow = 0
,@spidhigh = 32767

--------------------------------------------------------------
IF (@loginame IS NULL) --Simple default to all LoginNames.
GOTO LABEL_17PARM1EDITED
--------
-- select @sid1 = suser_sid(@loginame)
select @sid1 = null
if exists(select * from master.dbo.syslogins where loginname = @loginame)
select @sid1 = sid from master.dbo.syslogins where loginname = @loginame

IF (@sid1 IS NOT NULL) --Parm is a recognized login name.
begin
select @sidlow = suser_sid(@loginame)
,@sidhigh = suser_sid(@loginame)
GOTO LABEL_17PARM1EDITED
end
--------
IF (lower(@loginame) IN ('active')) --Special action, not sleeping.
begin
select @loginame = lower(@loginame)
GOTO LABEL_17PARM1EDITED
end
--------
IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0) --Is a number.
begin
select
@spidlow = convert(int, @loginame)
,@spidhigh = convert(int, @loginame)
GOTO LABEL_17PARM1EDITED
end

--------

RaisError(15007,-1,-1,@loginame)
select @retcode = 1
GOTO LABEL_86RETURN

LABEL_17PARM1EDITED:
-------------------- Capture consistent sysprocesses. -------------------
SELECT

spid
,status
,sid
,hostname
,program_name
,cmd
,cpu
,physical_io
,blocked
,dbid
,convert(sysname, rtrim(loginame))
as loginname
,spid as 'spid_sort'

, substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' '
+ substring( convert(varchar,last_batch,113) ,13 ,8 )
as 'last_batch_char'

INTO #tb1_sysprocesses
from master.dbo.sysprocesses (nolock)
where (dbid = @dbId or @dbName is null) and spid > 12




--------Screen out any rows?

IF (@loginame IN ('active'))
DELETE #tb1_sysprocesses
where lower(status) = 'sleeping'
and upper(cmd) IN (
'AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER'
)

and blocked = 0



--------Prepare to dynamically optimize column widths.


Select
@charsidlow = convert(varchar(85),@sidlow)
,@charsidhigh = convert(varchar(85),@sidhigh)
,@charspidlow = convert(varchar,@spidlow)
,@charspidhigh = convert(varchar,@spidhigh)



SELECT
@charMaxLenLoginName =
convert( varchar
,isnull( max( datalength(loginname)) ,5)
)

,@charMaxLenDBName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6)
)

,@charMaxLenCPUTime =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7)
)

,@charMaxLenDiskIO =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6)
)

,@charMaxLenCommand =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7)
)

,@charMaxLenHostName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8)
)

,@charMaxLenProgramName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11)
)

,@charMaxLenLastBatch =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9)
)
from
#tb1_sysprocesses
where
-- sid >= @sidlow
-- and sid <= @sidhigh
-- and
spid >= @spidlow
and spid <= @spidhigh



--------Output the report.


EXECUTE(
'
SET nocount off

SELECT
SPID = convert(char(5),spid)

,Status =
CASE lower(status)
When ''sleeping'' Then lower(status)
Else upper(status)
END

,Login = substring(loginname,1,' + @charMaxLenLoginName + ')

,HostName =
CASE hostname
When Null Then '' .''
When '' '' Then '' .''
Else substring(hostname,1,' + @charMaxLenHostName + ')
END

,BlkBy =
CASE isnull(convert(char(5),blocked),''0'')
When ''0'' Then '' .''
Else isnull(convert(char(5),blocked),''0'')
END

,DBName = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ')
,Command = substring(cmd,1,' + @charMaxLenCommand + ')

,CPUTime = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')
,DiskIO = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')

,LastBatch = substring(last_batch_char,1,' + @charMaxLenLastBatch + ')

,ProgramName = substring(program_name,1,' + @charMaxLenProgramName + ')
,SPID = convert(char(5),spid) --Handy extra for right-scrolling users.
from
#tb1_sysprocesses --Usually DB qualification is needed in exec().
where
spid >= ' + @charspidlow + '
and spid <= ' + @charspidhigh + '

order by spid_sort

SET nocount on
'
)
/*****AKUNDONE: removed from where-clause in above EXEC sqlstr
sid >= ' + @charsidlow + '
and sid <= ' + @charsidhigh + '
and
**************/
LABEL_86RETURN:

if (object_id('tempdb..#tb1_sysprocesses') is not null)
drop table #tb1_sysprocesses

return @retcode -- sp_who2

Finding duplicate indexes

It is always a good idea to check your databases for duplicate indexes. Duplicate indexes don’t do any good. The first question you ask yourself is why is it there in the first place? It might be the case it is not there but this is something that I have encountered in various occasions and it doesn’t harm to check if there are any duplicate indexes in your database. The next step is how to check; here I have tried to do this. The following script will help you find duplicate indexes on up to 5 columns. If you have got indexes on more that five columns, you can extend it to as many column as you want.

SELECT l1.tablename,
l1.indexname,
l2.indexname AS duplicateIndex
FROM
(SELECT tbl.[name] AS TableName,
idx.[name] AS IndexName,
INDEX_COL( tbl.[name], idx.indid, 1 ) AS col1,
INDEX_COL( tbl.[name], idx.indid, 2 ) AS col2,
INDEX_COL( tbl.[name], idx.indid, 3 ) AS col3,
INDEX_COL( tbl.[name], idx.indid, 4 ) AS col4,
INDEX_COL( tbl.[name], idx.indid, 5 ) AS col5
FROM SYSINDEXES idx
INNER JOIN SYSOBJECTS tbl ON idx.[id] = tbl.[id]
WHERE indid > 0
AND INDEXPROPERTY( tbl.[id], idx.[name], 'IsStatistics') = 0 ) l1
JOIN
(SELECT tbl.[name] AS TableName,
idx.[name] AS IndexName,
INDEX_COL( tbl.[name], idx.indid, 1 ) AS col1,
INDEX_COL( tbl.[name], idx.indid, 2 ) AS col2,
INDEX_COL( tbl.[name], idx.indid, 3 ) AS col3,
INDEX_COL( tbl.[name], idx.indid, 4 ) AS col4,
INDEX_COL( tbl.[name], idx.indid, 5 ) AS col5
FROM SYSINDEXES idx
INNER JOIN SYSOBJECTS tbl ON idx.[id] = tbl.[id]
WHERE indid > 0
AND INDEXPROPERTY( tbl.[id], idx.[name], 'IsStatistics') = 0) l2
ON l1.tablename = l2.tablename
AND l1.indexname <> l2.indexname
AND l1.col1 = l2.col1
AND COALESCE(l1.col2,'') = COALESCE(l2.col2,'')
AND COALESCE(l1.col3,'') = COALESCE(l2.col3,'')
AND COALESCE(l1.col4,'') = COALESCE(l2.col4,'')
AND COALESCE(l1.col5,'') = COALESCE(l2.col5,'')


Once you find your duplicate indexes, remove non-clustered indexes if one of the indexes is clustered.