CoreFTP: “Can’t find site profile”

Further to my post about using CoreFTP to automate FTPS transfers … I had an issue where the SQL Job-step kept failing with “cannot find profile!”.

The issue was caused by my configuring a ‘site-profile’ within CoreFTPs site-manager whilst logged in as myself. This was then stored in my registry settings and when the job-step executed as the SQL-Service-account it did not have access.

The fix (I chose) was to change the default-storage setting from the registry to a file (View / Options / Data / lock / admin / use default configuration file / lock / admin) then set-up the sites profile again.

Script to examine SQL Server 2005/08 logins

Adapting my previous “Script to examine SQL Server 2000 logins” for an upcoming SQL-2005 audit 🙂 …

-- sp_logins_report.sql
use dba

begin try drop table #rolemember, #dbnames, #report END TRY
BEGIN CATCH END CATCH -- ignore errors

declare @counter int
declare @dbname varchar(50)
declare @sqlstr nvarchar(4000)

--make table to hold database, user-define roles & user-names
create table #rolemember
rm_servername varchar(50) default @@servername,
rm_dbname varchar(1000),
rm_rolename varchar(1000),
rm_username varchar(1000),
rm_userid varchar(1000)

--make table to hold database names
create table #dbnames
(dbid int identity(1,1),
db_dbname varchar(50))

--make table to accumulate report
create table #report
re_servername varchar(50),
re_dbname varchar(1000),
re_rolename varchar(1000),
re_username varchar(1000),
re_userid varchar(1000)

--get members of each server role
insert into #rolemember (rm_rolename, rm_username, rm_userid)
exec dbo.sp_helpsrvrolemember

--get database names
insert into #dbnames (db_dbname)
select '[' + name + ']' from master.dbo.sysdatabases
where version > 0  -- online
set @counter = @@rowcount

--loop through databases to get members of database roles and user-defined roles
while @counter > 0

	--get database name from #dbnames table
	set @dbname = (select db_dbname from #dbnames where dbid = @counter)

	--get members of each database and user-defined role
	set @sqlstr = 'insert into #rolemember (rm_rolename, rm_username, rm_userid)
	exec ' + @dbname + '.dbo.sp_helprolemember'
	exec sp_executesql @sqlstr

	--update database name in rolemember table
	update #rolemember
	set rm_dbname = @dbname
	where rm_dbname is null

	set @counter = @counter - 1

--put data into report table
insert into #report
select rm.* from #rolemember rm
left join #report re
on rm.rm_username = re.re_username
and rm.rm_dbname = re.re_dbname
and rm.rm_rolename = re.re_rolename
and rm.rm_servername = re.re_servername
where re.re_servername is null

--display report
select re_username UserName, re_rolename RoleName, re_dbname DBName, re_servername ServerName
from #report
where re_username != 'dbo'
--order by re_username --display by user
order by re_rolename --display by role
--order by re_dbname --display by database