Post reply

database ownerShip and db hosting

  • December 10, 2003 at 2:03 am

    #59045

    Dear all,

    nearly,weekly I upload my web site to production server to enable my client o see the project progress (SQL 2000+ASP.net C# application)

    I host in company Called LFC Hosting, they gave me the UN and PW and SQL Server Name (Example: MySQLSrv.com/User1/Password1)

    Locally I connect to to db using the same username and password,

    But the problem when U Upload the db using "Import/export" feature in sqlserver (copy DB object) I find that the db object there (at hoster) is owned by (User1) but locally it is owned by (dbo)

    I want to solve this problem, By making my local tables is owned by "user1" not "dbo"

    Note: any DB schema changes done using "SQL server Enterprise Manager"

    and I log using "user1"

    locally user1 is "System admin" user on the server.

  • December 10, 2003 at 7:01 am

    #485523

    If User1 is a member of the sysadmin role for a particular SQL Server, that login will map into the database as dbo. That's why locally when you probably created the objects they belong to dbo and not User1.

    Locally, if you explicitly configure User1 to have access to the database and map into the database locally as User1, you'll be able to create objects under that user (as well as make "User1" owner of a object such as with sp_changeobjectowner). However, keep in mind that any new objects you create will be owned by dbo unless you explicitly tell SQL Server differently. For instance:

    CREATE TABLE User1.Test (TestID int)

    K. Brian Kelley, GSEC

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/

    K. Brian Kelley
    @kbriankelley

  • December 10, 2003 at 9:35 am

    #485571

    is there any way to make it owned by user1

    and what about the already created object, can I change the owner with this sp ?

    thanks for u support, this problem make me a nightmare

  • December 10, 2003 at 11:14 am

    #485583

    Yes, the way I describe establishes a user User1 in the database. This is necessary first. Go into Enterprise Manager | Security. Click on the User1 login, right-click, choose Properties. Click on the database access tab. Put a checkmark in the box beside the database you're using. This should show the user account to be User1. Click OK to save all the changes.

    In the database, any objects currently owned by dbo you want to change, use sp_changeobjectowner. The stored procedure is covered in Books Online.

    In the future, when you create new objects, make sure you specify the owner in the CREATE statement:

    CREATE TABLE User1.MyTable ()

    K. Brian Kelley, GSEC

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/

    K. Brian Kelley
    @kbriankelley

  • December 11, 2003 at 1:13 am

    #485661

    but we always use Enterprise manager to create database objects?

  • December 11, 2003 at 6:42 am

    #485693

    If you are dealing with tables, when you are in the Create Table screen, there is a Properties button (Hand pointing to a list) on the toolbar besides the Save button (floppy disk). In the properties you can set the owner before you save the object.

    K. Brian Kelley, GSEC

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/

    K. Brian Kelley
    @kbriankelley

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply