none
SQL Server 2000 Database to SQL Server 2005 Standard Security issue

    Question

  • We have experienced an issue with back backup / restore of a database originating from SQL Server 2000 to SQL Server 2003.

    We have the following setup:

    SQL Server 2000

      - DatabaseA

         - asdfUser (SQL User)

                   - asdfUser is (dbowner) of DatabaseA

      - DatabaseB

         - asdfUser (SQL User)

                   - asdfUser is (dbowner) of DatabaseB

    SQL Server 2005 Standard

      -asdfUser is NOT Setup as a user yet.

     

    -We restore DatabaseA and DatabaseB to the SQL Server 2005 Standard. The databases are restored with the security permissions of asdfUser being the DB Owner of DatabaseA and DatabaseB.

    -We create a new SQL user named asdfUser on the SQL Server 2005 box. We then try to add the UserMapping of DBOWNER for the DatabaseA and DatabaseB. We receive an error message stating that the asdfUser already have permissions to the databases. We proceed with the user creation without those permissions.

    -We proceed to the login properties of the asdfuser and view their UserMappings. The asdfUser does not have access to DatabaseA or DatabaseB. We then add the UserMapping of DBOWNER to both DatabaseA and DatabaseB. We Try to select OK and we receive an error message that states that the user already has those permissions.

    -When we query the UserID's of the asdfUser that is in the database and the UserID of the asdfUser that is created, they are two different values.

    I assume this is a bug... any word on a fix?

     

     

    Thursday, January 12, 2006 8:31 PM

Answers

  • I would need some additional information. Can you post the results of the following statements - just the rows corresponding to asdfUser and to the databases A and B:

    select name, sid, suser_sname(sid) from sys.server_principals where name = 'asdfUser' -- this will provide the SQL login information

    -- replace A and B with the respective database names in the query below
    select name, owner_sid, suser_sname(owner_sid) from sys.databases where name = 'A' or name = 'B' -- this will provide the database owner information

    Then, for each database, please execute:

    select name, sid, suser_sname(sid) from sys.database_principals where name = 'asdfUser' -- this will provide the SQL users information.

    Thanks
    Laurentiu

    Friday, January 13, 2006 2:25 AM
    Moderator