--##############ESpeed Database############
USE [iSynergy]
GO
declare @object nvarchar(50)
declare @objtype nvarchar(50)
DECLARE @SQL nvarchar(max)
--############Check for and change to dbo all objects owned by espeed user##################
declare espeedobjects cursor for
	select '['+[name]+']',[type] from sys.objects
		where principal_id = (select principal_id from sys.database_principals where [name] ='espeed')
	union
	select '['+[name]+']', 'fulltext catalog' as [type] 	from sys.fulltext_catalogs
		where principal_id = (select principal_id from sys.database_principals where [name] ='espeed')

open espeedobjects
fetch next from espeedobjects into @object, @objtype
while @@fetch_status = 0
begin
	set @sql = 'alter authorization on '+@objtype+' :: '+@object+' to dbo'
	exec sp_sqlexec @sql
	fetch next from espeedobjects into @object, @objtype
end
close espeedobjects
deallocate espeedobjects
--#############Drop database schema espeed if it exists#####################
IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'espeed')
BEGIN
	DROP SCHEMA [espeed]
END
--#############Drop database user espeed if it exists#####################
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'espeed')
BEGIN
	DROP USER [espeed]
END
--##############Check for espeed SQL login and create if necessary#################
USE [master]
IF  NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'espeed')
BEGIN
	CREATE LOGIN espeed WITH PASSWORD= 'deepse', DEFAULT_DATABASE= iSynergy
END
--##############add espeed login as user in iSynergy database and add owner role#############
Use [iSynergy]
CREATE USER espeed FOR LOGIN espeed WITH DEFAULT_SCHEMA = dbo
exec sp_addrolemember 'db_owner', 'espeed'
GO