|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Table NameHello,
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 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. -- Show quoteTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinpub.com "Justin" <Jus***@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 (Jus***@discussions.microsoft.com) writes:
> I am new at programming using a SQL Server back-end and it hasn't taken me Tom suggested using sp_rename, but that may only work if you are in > 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. 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, esq***@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 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" <esq***@sommarskog.se> wrote in message Justin (Jus***@discussions.microsoft.com) writes:news:Xns971BC09B6D0Yazorman@127.0.0.1... > I am new at programming using a SQL Server back-end and it hasn't taken me Tom suggested using sp_rename, but that may only work if you are in> 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. 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, esq***@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! Show quote "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" <esq***@sommarskog.se> wrote in message > news:Xns971BC09B6D0Yazorman@127.0.0.1... > Justin (Jus***@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, esq***@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 > > 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 Show quote "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. > > Justin  |
|||||||||||||||||||||||
Other interesting topics