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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-05-25 : 08:43:03
|
| Steve writes "I have a database named CB. The CB database has a table named ZipCodes.When I go to Enterprise Manager, under Users for that DB I see:dbo (sa)it user 'it' has no login.Now, under Security->Logins, I have a login called nldbIf I connect to the Database using the username nldb, I can perform a 'select * from ZipCodes' without any problems.I need to move this Database to another server. I need the new server to have the nldb login being able to query zipcodes directly (more below).I've tried import/export between databases (it fails with a vague error), and backup on the DB and restoring it in the other server. This works, but my big problem is that if I connect using nldb in the new server after the restore, if I do 'select * from ZipCodes' it says the object is invalid. To make it work I need to do 'select * from it.ZipCodes'. I've tried doing:grant all privileges on CB.* to nldbbut it didnt work (wrong syntax). I tried performing it on a single table, and still didn't work.Can anybody help me please to move the working Database and logins from one server to another?Thank you very much for taking the time to read it guys-Steve" |
|
|
jharwood
Starting Member
41 Posts |
Posted - 2004-05-25 : 09:01:58
|
| "To make it work I need to do 'select * from it.ZipCodes'. "If you run the output from this script on your new server, it should change the ownership of all "IT" objects to dbo. You should be able to query zipcodes without needing "it.zipcodes". select 'Exec sp_changeobjectowner '''+ ltrim(u.name) + '.' + ltrim(s.name) + ''''+', ''dbo'''from sysobjects s, sysusers uwhere s.uid = u.uidand u.name = 'it'and xtype in ('v','p','u','fn')order by s.name |
 |
|
|
jharwood
Starting Member
41 Posts |
|
|
|
|
|
|
|
Problem with Login, User and permissions on a DB.