Asking Username again and again before table name!!!
-
Hello friends..
I am facing some thing strange an new problem here in QA...
When I fired any select statement on any tables in my database ..it always says that that Invalid object name.... but when i wright username.tablename then i am getting RECORDS THAT HAVE...i am very irritating writing username again and again....last week i did replication part...here in local server that will affect such things???????.my login here is windows authetication...i changed to my username and password..and then fired select statement..but invain
I restore that database from my client side to local server...i created separate
user also but the things happenenig the same
Pls help me out!!!!
Regards,
Papillon
-
Go to Enterprise Manager under the Table icon. Look at the tables on the right of the screen and see if they are dbo or they have a specific user associated with them.
If they have a user associated with them, that is why you have to do this.
If you want to make them dbo, use the following: EXEC sp_changeobjectowner 'TableName', 'dbo'
GO
I wasn't born stupid - I had to study.
-
What username are you typing? Are you a member of DBO, and are all the tables created with an owner of DBO?
usernames as owners are there for a reason. From Books Online.
Owners and Permissions
Every object in Microsoft® SQL Server™ 2000 is owned by a user. The owner is identified by a database user identifier (ID). When an object is first created, the only user ID that can access the object is the user ID of the owner or creator. For any other user to access the object, the owner must grant permissions to that user. If the owner wants only specific users to access the object, the owner can grant permissions to those specific users.
If you want users to own their own tables, then your going to have to just type in the username. If you do not supply a username in your table declaration Sql Server looks for tables created by you, then tables that are owned by dbo. If it does not find either of those two, you get that message. the username/Tablename combination makes the table unique Joe.Orders is different than Tim.Orders.
If you do not want different owners It will be a little pain, but you can go through and change the owners of all the tables to dbo.
From BOL
This example changes the owner of the authors table to Corporate\GeorgeW.
EXEC sp_changeobjectowner 'authors', 'Corporate\GeorgeW' -
Hi!!!
The user i am using is the databse user X....that database i restored from my client side (there also user is X).....Our is windows authetication but i always logging in QA as logging name X and pwd as X....one interesting thing i observed here is that in QA when fired SELECT USER_NAME() I got DBO as output...I then fired SETUSER 'X' and then i fired again i got same result....So i created seperate logging name and user and gave all the permission to new user...and in QA when i logged with different user and fired above queries but still i got DBO as output......but the problem is only perticular that database..rest of the databases are working fine with their users....only problem is with this database which i restored here....
what i thinking is this problem is related to orphaned users???? or is related with Replication..because last week (before i updating this database from my client side) i did some work in Replication in the same database but i was not successful in that so i came out of it.....then two days after i restored (update ) my database....and then problem started....
Regards,
Papillon
-
Hi!!!
I got solution through my colleague...actually what i did big mistake was that when i created login there i assigned System Administrator Server Role to my login....when i unchecked that option i am getting my regular table as per my regular application and queries!!!
Let me thank u all who participated through this journey and also who try themself to get me out......
Regards,
Papillon
-
As a "best practice", you should always use at least the two part naming convention, which includes the user name. There's dozens of articles on this recommendation on the Web...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply