sql server 2008 db ownership question

  • Follow


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 


0
Reply Jason 4/12/2010 6:38:45 AM

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
> 


0
Reply Uri 4/12/2010 9:22:41 AM

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
>>
>
> 


0
Reply Jason 4/13/2010 1:35:23 AM

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
>>>
>>
>>
>
> 


0
Reply Uri 4/13/2010 6:37:58 AM

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
>>>
>>
>>
>
> 
0
Reply Tibor 4/13/2010 10:27:46 AM

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
>>>>
>>>
>>>
>>
>> 


0
Reply Jason 4/19/2010 12:26:51 AM

5 Replies
158 Views

(page loaded in 0.058 seconds)

10/21/2014 7:02:23 AM


Reply: