How to resolve a problem with owner (owner) when referencing an object

Source: Internet
Author: User

Problem Description:

Copy to a database db, appended, the object's owner is not admini, each query must write select * from admini.*** to be found. Is there any way to not use the front admini (without modifying the owner). Note:

Already under "Security-landing" also a new Admini user, the default database set to DB, permissions sufficient, but in Query Analyzer with Admini login, query or write Admini prefix, otherwise prompted object name is invalid

Problem Solving method:

If you are using SQL 2000, log in with a user, do not specify the owner, and access the object, the default owner is the current logged-on user

If it's 2005, the security of the data--the user--Right your user admin--attribute, see what the default architecture is, this default architecture determines that when you access an object, you don't specify the owner, you use that owner (in SQL 2005, owner becomes the architect)

will result in an exception that does not match the above argument:

If Admini is an orphaned user in database db, the situation is not the same as described above (attaching or recovering a database is very vulnerable to orphaned users), that is, the owner must be specified when referencing an object. Orphaned users can only create Admini logins and assign them permissions to the DB through the server role, or users who establish names in db that are not Admini are associated with logins.

To query for orphaned users in DB, execute the following statement:

Use DB


EXEC sp_change_users_login ' the "

Ways to resolve Orphaned users:

Resolve this exception as long as the orphaned user is resolved, after determining that Admini is orphaned, you can execute the following statement to resolve:

Use DB


--Fixing Orphaned users

EXEC sp_change_users_login ' Auto_Fix ', ' Admin ', NULL, ' password ';

--This password is the password that is assigned to the login when SQL automatically creates a login without prior admin login.

--Grant related permissions in db

EXEC sp_addrolemember ' db_owner ', ' Admin '

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.