SQL2008 Post Upgrade Problems

Question

Post reply

SQL2008 Post Upgrade Problems

gareth.vaughan

Old Hand

Points: 337
More actions
October 11, 2009 at 9:22 pm

#205443

Hi,
I have done an inplace upgrade from sql2000 to sql2008. Ther server is running windows server 2003 64 bit but the Sql install is 32 bit. I'm running sql2008 sp1.
I have the following problem:
When running one of my jobs it comes up with an error:
Msg 916, Sev 14, State 1, Line 1 : The server principal "sa" is not able to access the database "databasename" under the current security context. [SQLSTATE 08004]
testing under the sa login shows the database can of course be accessed... The part of the code which seems to trigger the error, is an insert into one database which triggers and insert into the 2nd database... promptly failing.
SA has full rights on the box. The job owner is a windows login which is a member of the serveradmin role. Its also local admin. the SA login is enabled as well.
Any suggestions would be greatly appreciated...
Cheers
Gareth

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

JDixon-385826 SSCrazy Points: 2723 More actions · Answer 1

JDixon-385826

SSCrazy

Points: 2723

October 11, 2009 at 11:08 pm

#1064581

Does the database have SA as the owner?

gareth.vaughan Old Hand Points: 337 More actions · Answer 2

thanks for the reply...

Yes it does.

When I first looked at this database it did not have sa mapped as a login and I got the same error as above (cannot use sa) when trying to add it. So I think it is related to this maybe.

I fixed this error by making sa the owner of the database which then mapped its login into it.

But I still get this error when the job runs....

Thanks

Gareth

EdVassie SSC Guru Points: 60472 More actions · Answer 3

Is the job owner account a sysadmin account? If the job owner is not a sysadmin account then it will not be able to use the sa security context.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

gareth.vaughan Old Hand Points: 337 More actions · Answer 4

Yes it is sa and local admin.

The other thing is, everything (including this job) was working fine until inplace upgrade.

If I run this same process under my own account also sysadmin I get the same error.

JDixon-385826 SSCrazy Points: 2723 More actions · Answer 5

just to make sure I would run sp_change_users_login with AUTO_FIX for every login involved.

I would also run sp_changedbowner against every database involved and set SA as the owner. If it is not disabled.

Also just wondering did you reboot the server?

EdVassie SSC Guru Points: 60472 More actions · Answer 6

Does the service account have the Impersonate a client after authentication (Internal Name: SeImpersonatePrivilege) privilege in Windows? This is needed to allow SQL to use different security contexts.

Another important right is Replace a process level token (Internal Name: SeAssignPrimaryTokenPrivilege). This is needed to allow SQL to set the run priority of a sub-task, but I do not think it is involved in authentication.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

gareth.vaughan Old Hand Points: 337 More actions · Answer 7

I've made the service account sa and local admin....

Not sure, but doesn't that mean it'll have every possible right.

Thanks guys

Yep the server has been rebooted.

EdVassie SSC Guru Points: 60472 More actions · Answer 8

The ability to use Windows privileges and membership of local Administrators group are two different things in current Windows operating systems. Even in NT4 you could limit the privileges that a local Admin had.

If your Windows people follow best practice they will use Group Polily Objects (GPOs) to control use of Windows privileges. You may need to talk to them to get the rights you need.

If you manually assign rights to an account that is controlled by a GPO, then the next time the GPO gets refreshed (normally every 90 minutes) the account rights will be set back to just what is given in the GPO.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.