February 8, 2008 at 10:34 am
I migrated our SQLserver to a larger server. For reasons I won't explain we backed up all the databases and then restored them on the new server. Of the 150 databases migrated there was one issue. The history tables are on a seperate database, data entered through a trigger.
The owner of both databases is dbo, user being the current sa. I receive an error that user cannot access the database under the current security context.
Both database are trustworthy.
I have compared the security and settings with other history database, triggers, and user setups and there is no difference I can find.
I have created a new history database and reaimed the triggers to it and get the same issue.
I find nothing unusual in the security of the primary database, it will take data input, and updates can occur with the trigger disabled.
I have re-written the triggers, using original typing script (not copying the script). All has failed.
Anyone have any ideas
February 8, 2008 at 11:10 am
Your database users don't match up to your logins due to different SID's.
Try running this:
sp_msforeachdb
'USE ?
DECLARE @username varchar(25)
DECLARE fixusers CURSOR
FOR
SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name
OPEN fixusers
FETCH NEXT FROM fixusers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login ''update_one'', @username, @username
FETCH NEXT FROM fixusers
INTO @username
END CLOSE fixusers
DEALLOCATE fixusers
'
February 8, 2008 at 3:55 pm
I checked the SIDs and they match.
Ran the script for the cursor and results were null.
Thanks.
February 8, 2008 at 4:21 pm
Do you use linked servers to access the history table in the other server if so your new sql server should be having its service account to access network resources.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply