sql server 2008 db ownership question - microsoft.public.sqlserver.server

Hi, In my SQL Server 2008, there is a database DB1 which is a database restore from SQL Server 2000 .bak file. In the SQL Server 2000, the DB1's owner is User1. But when I restored the bak file to 2008, the 2008 doesn't have the user User1, so the default owner of the DB1 in 2008 is dbo. I then added the User1 and changed the DB1's ownership to User1, and all the Tables under DB1 looks like the form of User1.Table However, in my query script, I have to type SELECT * FROM User1.Table If I just type SELECT * FROM Table it won't work. How do I fix this problem? Thanks for help. Jason

Jason That exactly the reason why MS introduce it. If you have a schema why would you abandon it? Add User1 to be member of sysadmin role (do you really needed it?) and then you can write SELECT * FROM Table "Jason Huang" <JasonHuang8888@hotmail.com> wrote in message news:e$swXrg2KHA.4716@TK2MSFTNGP06.phx.gbl... > Hi, > > In my SQL Server 2008, there is a database DB1 which is a database restore > from SQL Server 2000 .bak file. > In the SQL Server 2000, the DB1's owner is User1. But when I restored the > bak file to 2008, the 2008 doesn't have the user User1, so the default > owner of the DB1 in 2008 is dbo. > I then added the User1 and changed the DB1's ownership to User1, and all > the Tables under DB1 looks like the form of User1.Table > However, in my query script, I have to type > SELECT * FROM User1.Table > If I just type > SELECT * FROM Table > it won't work. > How do I fix this problem? > Thanks for help. > > > Jason >

Thanks Uri! The reason that I have to have the User1 to use the SELECT * FROM Table is in our front end, there are quite a few codes like that format. And the User1 is the member of sysadmin role already. I added some db_owner, db_accessadmin db role to User1. But still can't directy use the SELECT * FROM Table. In the Login property of the User1, the memember role mapping for DB1 is dbo, I'm not sure if this is correct. "Uri Dimant" <urid@iscar.co.il> ��g��l��s�D:ei4%23RGi2KHA.3568@TK2MSFTNGP04.phx.gbl... > Jason > That exactly the reason why MS introduce it. If you have a schema why > would you abandon it? > Add User1 to be member of sysadmin role (do you really needed it?) and > then you can write SELECT * FROM Table > > > > "Jason Huang" <JasonHuang8888@hotmail.com> wrote in message > news:e$swXrg2KHA.4716@TK2MSFTNGP06.phx.gbl... >> Hi, >> >> In my SQL Server 2008, there is a database DB1 which is a database >> restore from SQL Server 2000 .bak file. >> In the SQL Server 2000, the DB1's owner is User1. But when I restored >> the bak file to 2008, the 2008 doesn't have the user User1, so the >> default owner of the DB1 in 2008 is dbo. >> I then added the User1 and changed the DB1's ownership to User1, and all >> the Tables under DB1 looks like the form of User1.Table >> However, in my query script, I have to type >> SELECT * FROM User1.Table >> If I just type >> SELECT * FROM Table >> it won't work. >> How do I fix this problem? >> Thanks for help. >> >> >> Jason >> > >

Jason If he/she is a member of sysadmin server role it should work.Make sure that you connected as User1 "Jason Huang" <JasonHuang8888@hotmail.com> wrote in message news:uDzmUmq2KHA.556@TK2MSFTNGP04.phx.gbl... > Thanks Uri! > The reason that I have to have the User1 to use the SELECT * FROM Table is > in our front end, there are quite a few codes like that format. > And the User1 is the member of sysadmin role already. I added some > db_owner, db_accessadmin db role to User1. > But still can't directy use the SELECT * FROM Table. > In the Login property of the User1, the memember role mapping for DB1 is > dbo, I'm not sure if this is correct. > > > > > > > "Uri Dimant" <urid@iscar.co.il> > ��g��l��s�D:ei4%23RGi2KHA.3568@TK2MSFTNGP04.phx.gbl... >> Jason >> That exactly the reason why MS introduce it. If you have a schema why >> would you abandon it? >> Add User1 to be member of sysadmin role (do you really needed it?) and >> then you can write SELECT * FROM Table >> >> >> >> "Jason Huang" <JasonHuang8888@hotmail.com> wrote in message >> news:e$swXrg2KHA.4716@TK2MSFTNGP06.phx.gbl... >>> Hi, >>> >>> In my SQL Server 2008, there is a database DB1 which is a database >>> restore from SQL Server 2000 .bak file. >>> In the SQL Server 2000, the DB1's owner is User1. But when I restored >>> the bak file to 2008, the 2008 doesn't have the user User1, so the >>> default owner of the DB1 in 2008 is dbo. >>> I then added the User1 and changed the DB1's ownership to User1, and all >>> the Tables under DB1 looks like the form of User1.Table >>> However, in my query script, I have to type >>> SELECT * FROM User1.Table >>> If I just type >>> SELECT * FROM Table >>> it won't work. >>> How do I fix this problem? >>> Thanks for help. >>> >>> >>> Jason >>> >> >> > >

If the object exists in schema "User1" and you don't want to schema-qualify then you have to set the default schema for the user you connect as to "User1". -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Jason Huang" <JasonHuang8888@hotmail.com> wrote in message news:uDzmUmq2KHA.556@TK2MSFTNGP04.phx.gbl... > Thanks Uri! > The reason that I have to have the User1 to use the SELECT * FROM Table is > in our front end, there are quite a few codes like that format. > And the User1 is the member of sysadmin role already. I added some > db_owner, db_accessadmin db role to User1. > But still can't directy use the SELECT * FROM Table. > In the Login property of the User1, the memember role mapping for DB1 is > dbo, I'm not sure if this is correct. > > > > > > > "Uri Dimant" <urid@iscar.co.il> > ��g��l��s�D:ei4%23RGi2KHA.3568@TK2MSFTNGP04.phx.gbl... >> Jason >> That exactly the reason why MS introduce it. If you have a schema why >> would you abandon it? >> Add User1 to be member of sysadmin role (do you really needed it?) and >> then you can write SELECT * FROM Table >> >> >> >> "Jason Huang" <JasonHuang8888@hotmail.com> wrote in message >> news:e$swXrg2KHA.4716@TK2MSFTNGP06.phx.gbl... >>> Hi, >>> >>> In my SQL Server 2008, there is a database DB1 which is a database >>> restore from SQL Server 2000 .bak file. >>> In the SQL Server 2000, the DB1's owner is User1. But when I restored >>> the bak file to 2008, the 2008 doesn't have the user User1, so the >>> default owner of the DB1 in 2008 is dbo. >>> I then added the User1 and changed the DB1's ownership to User1, and all >>> the Tables under DB1 looks like the form of User1.Table >>> However, in my query script, I have to type >>> SELECT * FROM User1.Table >>> If I just type >>> SELECT * FROM Table >>> it won't work. >>> How do I fix this problem? >>> Thanks for help. >>> >>> >>> Jason >>> >> >> > >

Thanks! I finally tried the sp_change_users_login UPDATE_ONE, 'User1', 'User1'. Then I can do the SELECT * FROM Table. "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> ???????:efaV2Pv2KHA.5004@TK2MSFTNGP04.phx.gbl... > If the object exists in schema "User1" and you don't want to > schema-qualify then you have to set the default schema for the user you > connect as to "User1". > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > > "Jason Huang" <JasonHuang8888@hotmail.com> wrote in message > news:uDzmUmq2KHA.556@TK2MSFTNGP04.phx.gbl... >> Thanks Uri! >> The reason that I have to have the User1 to use the SELECT * FROM Table >> is in our front end, there are quite a few codes like that format. >> And the User1 is the member of sysadmin role already. I added some >> db_owner, db_accessadmin db role to User1. >> But still can't directy use the SELECT * FROM Table. >> In the Login property of the User1, the memember role mapping for DB1 is >> dbo, I'm not sure if this is correct. >> >> >> >> >> >> >> "Uri Dimant" <urid@iscar.co.il> >> ��g��l��s�D:ei4%23RGi2KHA.3568@TK2MSFTNGP04.phx.gbl... >>> Jason >>> That exactly the reason why MS introduce it. If you have a schema why >>> would you abandon it? >>> Add User1 to be member of sysadmin role (do you really needed it?) and >>> then you can write SELECT * FROM Table >>> >>> >>> >>> "Jason Huang" <JasonHuang8888@hotmail.com> wrote in message >>> news:e$swXrg2KHA.4716@TK2MSFTNGP06.phx.gbl... >>>> Hi, >>>> >>>> In my SQL Server 2008, there is a database DB1 which is a database >>>> restore from SQL Server 2000 .bak file. >>>> In the SQL Server 2000, the DB1's owner is User1. But when I restored >>>> the bak file to 2008, the 2008 doesn't have the user User1, so the >>>> default owner of the DB1 in 2008 is dbo. >>>> I then added the User1 and changed the DB1's ownership to User1, and >>>> all the Tables under DB1 looks like the form of User1.Table >>>> However, in my query script, I have to type >>>> SELECT * FROM User1.Table >>>> If I just type >>>> SELECT * FROM Table >>>> it won't work. >>>> How do I fix this problem? >>>> Thanks for help. >>>> >>>> >>>> Jason >>>> >>> >>> >> >>

drop database [using parameters]

361

Utf

SQL 2000 to SQL 2005 Upgrade. Upgrade advisor Errors

234

Utf

Moving SSIS 2005 packages from test to prod.

246

Utf

Steps for copying databases?

188

SQL

Using Float Type Variables for Primary on SQL 2005

199

Utf

SQL Server hotfix

Utf

view_metadata treated as materialized

165

JOH

linked server pushdown of query filtering to source database

242

Utf

xp_readerrorlog

338

tolcis

Exec Procedure within a View

115

Utf

8/11/2012 9:07:52 PM