May 26, 2010 at 7:22 am
Yesterday I upgraded one of my SQL 2005 boxes to SQL 2008 R2. The upgrade went perfect and eveything worked fine. The issue came this morning. I had a schedule job that runs every morning. The job runs as another user and now I'm getting an error. The error says:
"Executed as user: sde. Opening master list of databases cursor [SQLSTATE 01000] (Message 0) Inserting update statements for "FRANKLINGIS" database into temp table "#tables" [SQLSTATE 01000] (Message 0) The server principal "sde" is not able to access the database "franklingis" under the current security context. [SQLSTATE 08004] (Error 916). The step failed."
This is the script that I'm running:
declare @command varchar(255),
@database varchar(30),
@table varchar(50),
@fetch_inner int,
@fetch_outer int
--Temp table to process new tables
create table #tables (name varchar(100))
--Declare cursor to loop over all databases with SDE data
declare curdb cursor for select distinct database_name from sde.sde.sde_layers where database_name <> 'SDE' union select 'SDE'
print 'Opening master list of databases cursor'
print ''
--Open cursor and perform initial fetch
open curdb
fetch curdb into @database
--Save fetch status to local variable
select @fetch_outer = @@fetch_status
while @fetch_outer = 0
begin
--For each database, build a command to select all the tables
select @command = 'SELECT ''[''+ ' + @database + '.dbo.sysusers.name' + ' + ''].['' + ' +
@database + '.dbo.sysobjects.name + '']'' AS NAME
from ' + @database + '.dbo.sysobjects , '
+ @database + '.dbo.sysusers
where ' + @database + '.dbo.sysobjects.uid = ' + @database + '.dbo.sysusers.uid
and xtype = ''U'''
-- print @command
print 'Inserting update statements for "' + @database + '" database into temp table "#tables" '
insert into #tables exec(@command)
--Declare inner cursor to process each table
declare curtables cursor for select name from #tables
print 'Opening "#tables" table cursor for database: ' + @database
open curtables
fetch curtables into @table
--Save fetch status into local variable
select @fetch_inner = @@fetch_status
-- execute each record in the table
while @fetch_inner = 0
begin
print 'Updating stats on [' + @database + '].' + @table
select @command = 'update statistics [' + @database + '].' + @table
exec(@command)
-- print @command
fetch curtables into @table
select @fetch_inner = @@fetch_status
end
--Cleanup temp table and cursor
print ''
print 'Truncating "' + @database + '" records from "#tables" table and deallocating tables cursor'
truncate table #tables
close curtables
deallocate curtables
print 'Fetching the next database'
fetch curdb into @database
select @fetch_outer = @@fetch_status
end
--Clean up
print 'Final cleanup'
close curdb
deallocate curdb
drop table #tables
return
Any ideas?
May 26, 2010 at 7:24 am
Just so that I'm clear, I never had an issue with this job running under 2005.
Thanks again!
Jordon
May 26, 2010 at 8:13 am
Just as an update to this. I've tried running it as SA and a couple other accounts, with the same results. This make sense, since the sde account is a dbo and sysadmin.
May 26, 2010 at 8:25 am
Found the solution here:
http://www.sqlservercentral.com/Forums/Topic801367-146-1.aspx
June 13, 2011 at 3:27 am
Hi Jordan ,
Even I am having the same issue , I am getting this error on SQL SERVER 2008 R2 . I have looked into the fix URL .
I could not find . can I know what was fix for this issue.Your help is much appreciated.
Reagards
Murali
June 13, 2011 at 9:13 am
murali.jillellamudi 85871 (6/13/2011)
Hi Jordan ,Even I am having the same issue , I am getting this error on SQL SERVER 2008 R2 . I have looked into the fix URL .
I could not find . can I know what was fix for this issue.Your help is much appreciated.
Reagards
Murali
Murali,
I just did what was in this link:
http://www.sqlservercentral.com/Forums/Topic801367-146-1.aspx
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply