Remove LoginName Prefix from Tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Remove LoginName Prefix from Tables

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2010-01-23 : 18:50:55

For some reason all of my tables are prefixed with "azamsharp". Like "azamsharp.usp_getsomething". Is there anyway to remove the prefix "azamsharp" and just have "usp_getsomething".

Mohammad Azam
www.azamsharp.net

Kristen
Test

22859 Posts

Posted - 2010-01-24 : 05:48:36

sp_changeobjectowner ?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-24 : 10:48:06

if you want everything you create to be "dbo" then either change your default schema, or explicitly state dbo when creating objects:

create proc dbo.myStoredProc
AS
...

azamsharp
Posting Yak Master

201 Posts

Posted - 2010-01-24 : 12:04:09

how do I change the default schema?

Mohammad Azam
www.azamsharp.net

azamsharp
Posting Yak Master

201 Posts

Posted - 2010-01-24 : 12:17:55

I restored database from production so I cannot really alter the objects.

Mohammad Azam
www.azamsharp.net

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-24 : 12:18:07

quote:
Originally posted by azamsharp

how do I change the default schema?

Mohammad Azam
www.azamsharp.net

ALTER USER youruser WITH DEFAULT_SCHEMA = newschema;

azamsharp
Posting Yak Master

201 Posts

Posted - 2010-01-24 : 12:33:35

I just did that but nothing happened. When I say execute to new windows it generates the following code:

EXECUTE @RC = [GridViewGuy].[azamsharp].[usp_GetPopularArticles]

Mohammad Azam
www.azamsharp.net

azamsharp
Posting Yak Master

201 Posts

Posted - 2010-01-24 : 12:34:04

BTW I never had this problem when I was using SQL SERVER 2000

Mohammad Azam
www.azamsharp.net

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-24 : 12:38:38

quote:
Originally posted by azamsharp

BTW I never had this problem when I was using SQL SERVER 2000

Mohammad Azam
www.azamsharp.net

you have only owners in sql 2000. it doesnt have concept of schemas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-24 : 12:39:46

quote:
Originally posted by azamsharp

I just did that but nothing happened. When I say execute to new windows it generates the following code:

EXECUTE @RC = [GridViewGuy].[azamsharp].[usp_GetPopularArticles]

Mohammad Azam
www.azamsharp.net

what do you mean nothing happened? did you try creating objects? it will get created to specified schema by default

azamsharp
Posting Yak Master

201 Posts

Posted - 2010-01-24 : 13:17:48

I don't understand what do you mean creating objects? Is there any way you can connect to my computer remotely and see what is going on?

Mohammad Azam
www.azamsharp.net

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-24 : 13:21:28

quote:
Originally posted by azamsharp

I don't understand what do you mean creating objects? Is there any way you can connect to my computer remotely and see what is going on?

Mohammad Azam
www.azamsharp.net

creating objects like create table, create proc etc

azamsharp
Posting Yak Master

201 Posts

Posted - 2010-01-24 : 13:22:33

But I don't want to create objects since all the objects i.e. tables and sprocs are already there. I restored the database on my local machine from production so all the objects are there.

Mohammad Azam
www.azamsharp.net

azamsharp
Posting Yak Master

201 Posts

Posted - 2010-01-24 : 13:24:01

There are 10 tables in the database. Some are called "dbo.Feedbacks" and some are called "azamsharp.Articles". For Feedbacks I can do this and get the result:

select * from feedbacks

But for "articles" table I need to do the following:

select * from azamsharp.feedbacks

Mohammad Azam
www.azamsharp.net

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-24 : 13:29:49

quote:
Originally posted by azamsharp

There are 10 tables in the database. Some are called "dbo.Feedbacks" and some are called "azamsharp.Articles". For Feedbacks I can do this and get the result:

select * from feedbacks

But for "articles" table I need to do the following:

select * from azamsharp.feedbacks

Mohammad Azam
www.azamsharp.net

then do
ALTER USER yourusername WITH DEFAULT_SCHEMA = azamsharp;

then you can access them by
select * from feedbacks

azamsharp
Posting Yak Master

201 Posts

Posted - 2010-01-24 : 13:34:50

Actually for all the tables I don't want to prefix them with anything. If the table name is articles then I want to do

select * from articles

instead of select * from azamsharp.articles

Mohammad Azam
www.azamsharp.net

azamsharp
Posting Yak Master

201 Posts

Posted - 2010-01-24 : 13:37:06

I ran the following command:

ALTER USER azamsharp WITH DEFAULT_SCHEMA = dbo

but nothing changed. Everything is exactly the same.

Mohammad Azam
www.azamsharp.net

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-24 : 13:42:46

quote:
Originally posted by azamsharp

I ran the following command:

ALTER USER azamsharp WITH DEFAULT_SCHEMA = dbo

but nothing changed. Everything is exactly the same.

Mohammad Azam
www.azamsharp.net

thats because your default schema was dbo before also which is why you could access dbo tables without explicitly specifying dbo

azamsharp
Posting Yak Master

201 Posts

Posted - 2010-01-24 : 13:44:54

Even if I change the user = "azamsharp" default schema to "dbo" it does not allow me to perform the following queries:

select * from articles.

It does not recognize the articles. The following query runs fine:

select * from azamsharp.articles.

Mohammad Azam
www.azamsharp.net

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-24 : 13:48:43

quote:
Originally posted by azamsharp

Even if I change the user = "azamsharp" default schema to "dbo" it does not allow me to perform the following queries:

select * from articles.

It does not recognize the articles. The following query runs fine:

select * from azamsharp.articles.

Mohammad Azam
www.azamsharp.net

you're missing my point altogether!
you can always have only a single schema as your default
once selected that, you can access all objects belonging to it using their names alone. for all others you need to use schemaname.objectname

azamsharp
Posting Yak Master

201 Posts

Posted - 2010-01-24 : 14:09:07

One thing that is strange is that I use SQL SERVER authentication to log in to the production system. I think it is because of the production credentials that they are mapping to the correct schema and allowing me the run queries without specifying the prefix.

On my local machine I use windows authentication. Maybe if I can create sql server authentication on my local machine it will behave like production.

Mohammad Azam
www.azamsharp.net

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources