February 5, 2010 at 8:29 am
Hi,
Have created a brand new database in SQL 2005 for a new Application. The data will be accessed by a Named_Scribe.
Need to access data from a second Database, already in existance in SQL 2000. Created a new database in 2005 and then restored the SQL 2000 Database over it.
Deleted the Named_Scribe from both Databases and server, and then recreated it, to avoid any orphan issues.
If I run EXECUTE AS USER = 'Named_Scribe' SELECT * FROM MyTable in the second Database I get the required data.
If I run EXECUTE AS USER = 'Named_Scribe' select * FROM second_database.dbo.MyTable against the first (new) database I get an error "The server proncipal Named_Scribe" is not able to access second_database under the current security context"
I have added DB Chaining and turned TRUSTWORTHY on. The login has been completely deleted and recreated twice and I have carefully checked the permissions under logins in both Database and under server logins.
Both databases are owned by sa.
I cannot think of anything else to try at the moment. Although this link sounded promising but I could not locate the column headers mentioned in section 3.
http://www.kf7.co.uk/sql-server-principal-not-able-access-database.aspx
Any help or suggestions most welcome. Thank you.
Colin
February 7, 2010 at 10:24 am
Orphaned login.
Run
exec sp_change_users_login 'report'
in the upgraded database and see how many users need to have thier SIDS synchronized or don't exist as logins on the server.
you can fix it with the same stored procedure by either creating the logins (recommended) and synching the sids or auto fixing the orphaned login. See the Books Online for information on sp_change_users_login to learn about this common problem.
http://msdn.microsoft.com/en-us/library/ms174378(SQL.90).aspx
Craig Outcalt
February 8, 2010 at 3:49 am
Craig,
Thank you. All is cear again after a weekend away! Thanks for your help and my issue is resolved.
Colin
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply