Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
jgallen23
Starting Member
17 Posts |
Posted - 2006-12-15 : 13:25:53
|
I just backed up my sql 2000 database from my hosting provider to my local computer so I don't have to make changes directly on production. When I backed it up, it kept the user tsp as the owner of all of the tables and procedures. So now whenever I login I have to type select * from tsp.games instead of just games. Also, I can't create the login tsp because it says it already exists, but it really doesn't because I can't login as that user. Any ideas on how to create the tsp user on the local database? |
|
vaddi
Posting Yak Master
145 Posts |
Posted - 2006-12-15 : 13:39:45
|
Go to the sysusers table in the master table of the database . Locate the user tsp and delete him. In the security - logins , remap the user back to the database with his permissions. |
|
|
jgallen23
Starting Member
17 Posts |
Posted - 2006-12-15 : 13:49:56
|
it says I cannot delete the row. It says that Ad Hoc updates are not enabled. Any ideas? |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-12-15 : 14:21:40
|
Create a login called TSP and then run this in the database you restored:exec sp_change_users_login @Action = 'Update_One', @UserNamePattern = 'TSP', @LoginName = 'TSP' CODO ERGO SUM |
|
|
jgallen23
Starting Member
17 Posts |
Posted - 2006-12-15 : 14:35:45
|
is there any way to reset the owner to dbo so anybody can access it? |
|
|
jgallen23
Starting Member
17 Posts |
Posted - 2006-12-15 : 14:39:18
|
Michael,I did that, but when I login as tsp it still doesn't let me select * from games, it still needs select * from tsp.games |
|
|
jgallen23
Starting Member
17 Posts |
Posted - 2006-12-15 : 16:16:51
|
I found a sweet script to change all the objects back to dbo.You can find it here:http://weblogs.asp.net/dneimke/archive/2003/11/10/36691.aspx |
|
|
|
|
|