sql server 2008 db ownership question
in [Server Configuration]
|
From: Jason Huang on 12 Apr 2010 02:38 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
From: Uri Dimant on 12 Apr 2010 05:22 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(a)hotmail.com> wrote in message news:e$swXrg2KHA.4716(a)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 >
From: Jason Huang on 12 Apr 2010 21:35 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(a)iscar.co.il> ���g��l��s�D:ei4%23RGi2KHA.3568(a)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(a)hotmail.com> wrote in message > news:e$swXrg2KHA.4716(a)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 >> > >
From: Uri Dimant on 13 Apr 2010 02:37 Jason If he/she is a member of sysadmin server role it should work.Make sure that you connected as User1 "Jason Huang" <JasonHuang8888(a)hotmail.com> wrote in message news:uDzmUmq2KHA.556(a)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(a)iscar.co.il> > ���g��l��s�D:ei4%23RGi2KHA.3568(a)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(a)hotmail.com> wrote in message >> news:e$swXrg2KHA.4716(a)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 >>> >> >> > >
From: Tibor Karaszi on 13 Apr 2010 06:27 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(a)hotmail.com> wrote in message news:uDzmUmq2KHA.556(a)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(a)iscar.co.il> > ���g��l��s�D:ei4%23RGi2KHA.3568(a)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(a)hotmail.com> wrote in message >> news:e$swXrg2KHA.4716(a)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 >>> >> >> > >
|
Next
|
Last
Pages: 1 2 Prev: Can't see default instance of sql 2008; login name oddness Next: Speed in select statement |