February 16, 2011 at 3:33 pm
Hello everyone
some background
I have "upgraded" a SQL server 2000 SP4 installation to SQL 2008 R2, I'm using quotation marks as I didnt run the in-place upgrade option.
Fixed things that the upgrade advisor complained about.
I backed up the user databases and scripted out logins, jobs, operators, DTS packages etc.
Uninstalled the SQL 2000, installed SQL 2008 R2 with cu5 and then restored databases, scripted back everything, fixed orphaned users and such.
Most things seem to work just fine with one exception, our stored procedures that manipulate data in tables in another database on the same server lacks permissions. This worked perfectly on SQL 2000.
If I look at the properties for the allready existing sprocs they have the "Execute as" property set as "Caller" and Im thinking this is the core reason of my problem.
The database users dont have insert or update on the table in the other database only "select" and I really dont want to change this since the security is bad as it is allready.
Anyhow..
is the default behavior of sprocs changed since 2000? We havent recreated the sprocs so it seems like even existing sprocs behavior are changed when upgrading.
If I recreate the sprocs using "with execute as owner" and run "alter database [db_name] trustworthy on" in the originating database it will work but we have over 2000 sprocs in sourcesafe so its not really an appealing task. Is it a way of changing this on the server level or something so that it will behave as it did on sql 2000? It seems like the default behavior for sprocs in SQL 2000 was to run as owner, right?
so to sum it up:
public has execute on sproc in originating database
sa is owner of the sprocs
sproc reads/manipulates a table in another database where public has select permissions only
2008 R2: the sproc can select data but not insert/update
2000 SP4: the sproc can select/insert/update data
I know this is really bad in a security perspective, this is an old badly programmed application and an audit has been run so there will eventually be changes in the application permission-wise. Sadly enough not in time for the SQL 2008 R2 upgrade so I need to fix this without lowering security even more than in the sql 2000 case and hopefully without recreating every sproc also.
Am I on the right track or is it something else completly that's causing theese errors?
any suggestions and thoughts will be appreciated
Thanks in advance
/Niclas
edit: forgot to mention that all databases are running in compatibility mode 80
February 21, 2011 at 8:32 am
Ok, the sollution was so easy it's embarrassing.
The new property "execute as" threw me in the wrong direction.
Just had to enable cross database ownership chaining to get the sprocs to behave as expected.
And Yes I know this is not a good thing and it will be sorted in due time but at least its not less secure than it was before the upgrade.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply