|
|
You're in the
sql server programming
group:Table Name
sql server programming:
I am new at programming using a SQL Server back-end and it hasn't taken me long to run into some trouble...any help would be appreciated. When tables are imported by users, the table names are [username].[tablename] (for example, Jennifer.tblCustomers)...when I try to reference the table using the [tablename], it doesn't work. But, when I use the [username].[tablename], it works. Is there a way to make all tables that are imported dbo.[tablename]? This scenario seems to work best b/c it would make the 'prefix' standard for all tables throughout the database. Thank you for any help you can offer.
You can rename a table with sp_rename. Perhaps the import process can be
changed to create the table with owner dbo and you can avoid the problem at the source. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinpub.com [quoted text, click to view] "Justin" <Justin@discussions.microsoft.com> wrote in message news:28147662-2E94-4B00-B15E-139AE6CB2FC9@microsoft.com... > Hello, > > I am new at programming using a SQL Server back-end and it hasn't taken me > long to run into some trouble...any help would be appreciated. > > When tables are imported by users, the table names are > [username].[tablename] (for example, Jennifer.tblCustomers)...when I try > to > reference the table using the [tablename], it doesn't work. But, when I > use > the [username].[tablename], it works. Is there a way to make all tables > that > are imported dbo.[tablename]? This scenario seems to work best b/c it > would > make the 'prefix' standard for all tables throughout the database. > > Thank you for any help you can offer. > > Justin
Justin (Justin@discussions.microsoft.com) writes:
[quoted text, click to view] > I am new at programming using a SQL Server back-end and it hasn't taken me > long to run into some trouble...any help would be appreciated. > > When tables are imported by users, the table names are > [username].[tablename] (for example, Jennifer.tblCustomers)...when I try > to reference the table using the [tablename], it doesn't work. But, > when I use the [username].[tablename], it works. Is there a way to make > all tables that are imported dbo.[tablename]? This scenario seems to > work best b/c it would make the 'prefix' standard for all tables > throughout the database. Tom suggested using sp_rename, but that may only work if you are in Canada. :-) In the rest of the world sp_changeobjectowner may be a better bet. Of course, changing the import process may be an option, but without knowing anything about your import process, it's difficult to say whether this can be achieved or not. If the import process creates tables, this would require users create tables owned by dbo, which has some security implications. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
You probably shouldn't have users creating tables in your database. Really
only the DBA or developers should be creating tables in order to maintain some kind of integrity and control over the data. Are you creating relationships for them, or proper indexes? If your users do have to import large volumnes of data, why not INSERT into an existing table? Having said that, if when creating a table you prefix it with the owner it will be created as such eg : CREATE TABLE dbo.customers ... There's no guaranteeing your users will have SELECT permissions on tables created this way, but that's a separate issue. Strictly speaking you should always prefix your objects with the database name and the owner; this is called three-part naming; eg northwind.dbo.users Just out of interest, what does your DBA think of your database design? Damien [quoted text, click to view] "Justin" wrote:
> Hello, > > I am new at programming using a SQL Server back-end and it hasn't taken me > long to run into some trouble...any help would be appreciated. > > When tables are imported by users, the table names are > [username].[tablename] (for example, Jennifer.tblCustomers)...when I try to > reference the table using the [tablename], it doesn't work. But, when I use > the [username].[tablename], it works. Is there a way to make all tables that > are imported dbo.[tablename]? This scenario seems to work best b/c it would > make the 'prefix' standard for all tables throughout the database. > > Thank you for any help you can offer. >
Duh! I really need a coffee.
-- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinpub.com .. [quoted text, click to view] "Erland Sommarskog" <esquel@sommarskog.se> wrote in message news:Xns971BC09B6D0Yazorman@127.0.0.1... Justin (Justin@discussions.microsoft.com) writes: > I am new at programming using a SQL Server back-end and it hasn't taken me > long to run into some trouble...any help would be appreciated. > > When tables are imported by users, the table names are > [username].[tablename] (for example, Jennifer.tblCustomers)...when I try > to reference the table using the [tablename], it doesn't work. But, > when I use the [username].[tablename], it works. Is there a way to make > all tables that are imported dbo.[tablename]? This scenario seems to > work best b/c it would make the 'prefix' standard for all tables > throughout the database. Tom suggested using sp_rename, but that may only work if you are in Canada. :-) In the rest of the world sp_changeobjectowner may be a better bet. Of course, changing the import process may be an option, but without knowing anything about your import process, it's difficult to say whether this can be achieved or not. If the import process creates tables, this would require users create tables owned by dbo, which has some security implications. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Thank you all for your responses! Sorry I haven't checked in on this for a
while. I'm going try your suggestions and I'll get back with you. THANKS! [quoted text, click to view] "Tom Moreau" wrote:
> Duh! I really need a coffee. > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > SQL Server MVP > Columnist, SQL Server Professional > Toronto, ON Canada > www.pinpub.com > .. > "Erland Sommarskog" <esquel@sommarskog.se> wrote in message > news:Xns971BC09B6D0Yazorman@127.0.0.1... > Justin (Justin@discussions.microsoft.com) writes: > > I am new at programming using a SQL Server back-end and it hasn't taken me > > long to run into some trouble...any help would be appreciated. > > > > When tables are imported by users, the table names are > > [username].[tablename] (for example, Jennifer.tblCustomers)...when I try > > to reference the table using the [tablename], it doesn't work. But, > > when I use the [username].[tablename], it works. Is there a way to make > > all tables that are imported dbo.[tablename]? This scenario seems to > > work best b/c it would make the 'prefix' standard for all tables > > throughout the database. > > Tom suggested using sp_rename, but that may only work if you are in > Canada. :-) > > In the rest of the world sp_changeobjectowner may be a better bet. > > Of course, changing the import process may be an option, but without > knowing anything about your import process, it's difficult to say > whether this can be achieved or not. If the import process creates > tables, this would require users create tables owned by dbo, which > has some security implications. > > -- > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |