Check current database user and login user

execute as login = 'TFS4DBA' -------- server login
execute as user='afm' --------- database user

select current_user,SESSION_USER, USER ,system_user,SUSER_SNAME(),SUSER_SID()

Change object owner in SQL Server 2005

EXEC sp_changeobjectowner 'web.rego_for_dism', 'dbo'

------

The following small SQL code snippet goes through all user tables in the database and changes their owner to dbo. It uses sp_changeobjectowner system stored procedure:



DECLARE tabcurs CURSOR
FOR
SELECT 'SOMEOWNER.' + [name]
FROM sysobjects
WHERE xtype = 'u'

OPEN tabcurs
DECLARE @tname NVARCHAR(517)
FETCH NEXT FROM tabcurs INTO @tname

WHILE @@fetch_status = 0
BEGIN

EXEC sp_changeobjectowner @tname, 'dbo'

FETCH NEXT FROM tabcurs INTO @tname
END
CLOSE tabcurs
DEALLOCATE tabcurs

disable and enable jobs

USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_id=N'51228a93-6a7c-4086-8cc2-bd1dfa60677d',
@enabled=1
GO


USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_id=N'51228a93-6a7c-4086-8cc2-bd1dfa60677d',
@enabled=0
GO

SSIS Package Configuration in SQL Server 2005

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

Monitoring log shipping in SQL Server 2005

select * from msdb..log_shipping_monitor_alert

select * from msdb..log_shipping_monitor_primary
select * from msdb..log_shipping_monitor_secondary

select * from msdb..log_shipping_monitor_error_detail
select * from msdb..log_shipping_monitor_history_detail

SQL Server Agent jobs

Disable All SQL Server Agent Jobs

USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1;
GO

Enable All SQL Server Agent Jobs

USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 1
WHERE Enabled = 0;
GO

Disable Jobs By Job Name

USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE [Name] LIKE 'Admin%';
GO

Disable Jobs By Job Category

USE MSDB;
GO
UPDATE J
SET J.Enabled = 0
FROM MSDB.dbo.sysjobs J
INNER JOIN MSDB.dbo.syscategories C
ON J.category_id = C.category_id
WHERE C.[Name] = 'Database Maintenance';
GO

How to connect an SQL Server 2005 db to ODI

http://bloggingaboutoracleapplications.org/how-to-connect-an-sql-server-2005-db-to-odi/


JDBC URL use the following format: jdbc:sqlserver://:



http://msdn.microsoft.com/en-us/data/aa937724.aspx

Download SQL Server JDBC Driver 3.0 (Support SQL Server 2008)