Having troubles accessing data after the upgrade to SQL Server 2005
-
Yesterday some DBA’s came over to help me upgrade our test server from SQL Server 2000 to SQL Server 2005. We had gotten the new test server with SQL Server 2000 as a test bed to see what the upgrade process was like. It was my understanding that the new machine came with SQL 2000 and all applicable service packs applied. This turned out to not be the case! None of SQL Server 2000 service packs were applied. However, after spending two and a half hours at it the Oracle DBA’s decided to uninstall SQL 2000. They did that and then attached the .MDF files to SQL 2005, which upgraded them all.
That was neat, and I thought all was cool. But I am wrong. Our practice is to create a SQL Server user, which I’ll call OrdinaryUser, and give it appropriate permissions on tables, views and stored procedures, in SQL 2000. Now OrdinaryUser cannot be used to open anything at all! This means that all of our applications will fail to run against SQL Server 20005, which ain’t too cool.
So, I am trying to figure out how to fix this problem. I got into SQL Server Management Studio and opened up the OrdinaryUser in the Security folder of each database. I then tried to add all of our tables, views and stored procedures to the Securables page. I got an error that says, ”Login name must be specified (SqlManagerUI)”. However, the textbox for entering the login name is disabled, so I cannot enter it.
I then thought maybe I could delete OrdinaryUser and re-create it, and give it permissions to all of the tables, views and stored procedures we need to give it access to. However, I cannot delete OrdinaryUser because when I try I get the error: “The database principal owns a schema in the database, and cannot be dropped. (Microsoft Server, Error: 15138)”
So, I cannot add tables, views and stored procedures to OrdinaryUser, and I cannot delete OrdinaryUser, and none of our software will run against any of the SQL Server 2005 databases.
What do I do?
Kindest Regards, Rod Connect with me on LinkedIn.
-
First of all : don't panick
If you still have a copy of the sql2000 (or a backup of it):
have a look at http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=146&messageid=306372#bm306479
(Transfer SQL 2000 logins to SQL2005)
If you use the version with the SID=xx you'll get it immediatly to work because your db-sids will be matching.
Else you 'll need to synchonize them using :
sp_change_users_login @Action = 'Update_One',@UserNamePattern = 'yoursqluser', @LoginName = 'yoursqluser'
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
No, the DBA's uninstalled SQL 2000.
So, what do I do now? Is the only way to fix this problem is by using SQL Server 2000?
Kindest Regards, Rod Connect with me on LinkedIn.
-
The sid (security ID) of OrdinaryUser as a user in your attached database must match the sid of the OrdinaryUser login you created on the server. Simply creating a login with the same name gets you nowhere. Whether you create the login (without the old sid) before or after you attached databases from another server, a new random sid value is generated. You have to create the login with the correct sid in the first place if you want to avoid trouble, either by scripting logins or transferring them from the old server to the new one while the old server is still functional.
Creating an OrdinaryUser login that matches the user in the attached database would be best if there are multiple databases, or if you forsee attaching or restoring any other databases from the old server. Throw away the exising login and create one that works like this:
DROP LOGIN OrdinaryUser
EXEC sp_change_users_login 'Auto_Fix', 'OrdinaryUser', NULL, '<pwd>'
On the other hand if you can't drop the login because you already have used it for other things, you can modify the orphaned user in the database to match the login:
EXEC sp_change_users_login 'Auto_Fix', 'OrdinaryUser'
If that doesn't work, you should be able to get the sid from the databases that have been upgraded and create a matching login manually. Run the first query to get the sid value, then cut-and-paste it into the CREATE LOGIN.
SELECT sid FROM sys.sysusers WHERE name = 'OrdinaryUser'
DROP
LOGIN OrdinaryUser
CREATE LOGIN OrdinaryUser WITH PASSWORD='<pwd>', SID= <sid>
By the way, this is not just an upgrade problem, it occurs any time you attach or restore a database from one server to another. Windows logins (in the same domain) are not a problem because they get their SID from the domain, but if you use SQL logins on multiple servers you should make sure they have the same SID on every server.
-
alzdba, the sp_change_users_login system stored procedure did the trick, thank you very much!!!
Kindest Regards, Rod Connect with me on LinkedIn.
-
I have also had the problem on a continuing bases and kept creating new logins everytime we migrated DBs to a new instance of SQL2005.
The process mentioned above worked when I used the SELECT statement to retrieve the SID and then the CREATE statement to fix things. The drop statement never would work without error.
So the short story is that this worked fine:
SELECT sid FROM sys.sysusers WHERE name = <login>
CREATE LOGIN <login> WITH PASSWORD='password', SID= <sid>
Thanks for the help.
John
-
alzdba, that did er well. Thanks! Worked for me against a SQL Express database.
Wayne
-
People:
Please I need help.
I have restored a database with the following query:
RESTORE DATABASE mydb
FROM DISK = 'C:\DataBase\mydb'
WITH MOVE 'mydb' TO 'C:\Archivos de programa\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydb.MDF',
MOVE 'mydb_log' TO 'C:\Archivos de programa\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydblog_log.LDF'
After that I run the fix login as it is explained above (EXEC sp_change_users_login 'Auto_Fix', 'sysdba') . I have configured in the login the default database and mapped the default schema.
But when I log on using that ¨login¨ even though I am in the default database, I have to write "schema"."table" for any query I want to run. (For example,I log to SQL Server with user SYSDBA and when I run SELECT * FROM DOCUMENTS I get an error reported, but if I set SELECT * SYSDBA.DOCUMENTS, everything works fine)
Can somebody help me please? Thanks in advanced.
-
Best is to post your question in a new thread.
Check the default schema for this user in the restored database.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
ALZDBA (3/21/2010)
Best is to post your question in a new thread.Check the default schema for this user in the restored database.
Thanks for your help and sorry for posting here.
I had already checked and it is the right one. The only ¨"problem" i have noticed is that when I edit the user (in the restored database) using SQL Management Studio, in "Securable", but when I add the objects of desired schema, and I GRANT them, it is like no change is performed. Once I edit the user again, this changes are not saved.
Thank you.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply