none
Database/Object Ownership Misalignment

    Question

  • I have a suite of database that have had a security scan ran against them and it reports every object in my dtabases are owned by some other logon that did not create the databases nor did it ever have a reason to be owner.  My guess is that they got manually changed somehow.  However, I need to change them back to the dbo.

    I've used sp_changeobjectowner and it doesn't find any objects owned by the roque logon.  Then doin gsome more reading it's my understanding that in 2008R2 it really isn't the desired way.  So my question is I have a dozen databases, with an object count to the low side of around 100 and to the high side almost 1000.  Does anyone have any quick tips, code sample for looping through all the objects in a selected database for any objects not owned by the dbo.

    I appreciate any help forward on this, thanks.

    Wednesday, December 19, 2012 11:53 PM

Answers

  • Somewhat in contrary to what I expected, but the conclusion is the same: the sid for dbo in the database is not the same as the sid in sys.databases, and this is why your security tool goes ballistic. You should fix this by changing the database owner with

    ALTER AUTHORIZATION ON DATABASE::yourdb TO rightowner

    Cavates: 1) I did the syntax from memory. 2) You may have to do it in two steps, that first change owner sa and then to the correct owner.

    Furthermore, you should add this to the routine when you move databases from one server to another.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by WritinCode Friday, December 21, 2012 3:18 PM
    Friday, December 21, 2012 8:01 AM

All replies

  • >>>>I need to change them back to the dbo. See the output 

    SELECT 'EXEC sp_changeobjectowner '''+ROUTINE_NAME+''',''dbo'''
        FROM INFORMATION_SCHEMA.ROUTINES
         WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+'.'+ROUTINE_NAME),
    'IsMsShipped')=0
         AND ROUTINE_SCHEMA != 'dbo'


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, December 20, 2012 6:26 AM
  • So what is the owner of the object according to sys.objects? What does these two queries return:

    select principal_id, schema_id, count(*) from sys.objects
    group by principal_id, schema_id
    
    select s.principal_id, count(*)
    from     sys.schemas s
    where    exists (select * from sys.objects o where s.schema_id = o.schema_id)
    group    by s.principal_id

    My gut reaction to the report from the security scan is "Hogwash!". If I am to make any guess, what has happened is that the database has been restored from another server where it was owned by an SQL login, and now there is a misalignment for the database owner between the database and the server. This is something you should address, as there is situations where this can give you funny errors.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, December 20, 2012 8:57 AM
  • This returns zero (0) rows
    Thursday, December 20, 2012 1:15 PM
  • Erland, you are correct that these databases have come from another server.  They are built and populated on an external server and then moved over to the actual production server during off hours.  The business logic is that there is a great deal of work that goes on that could result in error, so a working copy is where the at risk work is performed and then restored over the production databases once all the at risk work is completed, this work could take days based on the orgins of the data.  Not ideal, but its the hand I've been dealt :(

    The 2 queries you provided return the following result sets.

    select principal_id, schema_id, count(*) from sys.objects
    group by principal_id, schema_id

    --principal_id SCHEMA_ID  
    -------------- ----------- -----------
    --NULL         1           688
    --NULL         4           50

    select s.principal_id, count(*)
    from     sys.schemas s
    where    exists (select * from sys.objects o where s.schema_id = o.schema_id)
    group    by s.principal_id

    --principal_id
    -------------- -----------
    --1            1
    --4            1

    The principal_id's relate as this, 1 = dbo, and 4 = sys

    This is pretty much what I have been coming up with with other different queries, however the scans continue to show another logon as having object ownership.  I have yet to been able to find a single object where that account actually owns in our databases.

    Schott

    Thursday, December 20, 2012 1:38 PM
  • Your post confirms my assumptions.

    Try this SELECT:

    SELECT suser_sname(sid) FROM sys.database_principals WHERE principal_id = 1

    If my assumption is correct, it will return NULL. Or at least a value that is different from what you get from

    SELECT suser_sname(owner_sid) FROM sys.databases WHERE name= 'yourdb'

    This happens because SQL logins with the same name have different SIDs on different servers.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, December 20, 2012 10:27 PM
  • Thanks again Erland.

    The first script returns the name that should not be while the second one returns NULL

    Friday, December 21, 2012 12:28 AM
  • Somewhat in contrary to what I expected, but the conclusion is the same: the sid for dbo in the database is not the same as the sid in sys.databases, and this is why your security tool goes ballistic. You should fix this by changing the database owner with

    ALTER AUTHORIZATION ON DATABASE::yourdb TO rightowner

    Cavates: 1) I did the syntax from memory. 2) You may have to do it in two steps, that first change owner sa and then to the correct owner.

    Furthermore, you should add this to the routine when you move databases from one server to another.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by WritinCode Friday, December 21, 2012 3:18 PM
    Friday, December 21, 2012 8:01 AM
  • I have run the alter authorization on the suite of databases.  In the sys.database_principals the offending name exists in the suser_sname(sid) column for the principal_id 1, name = dbo, type = s, default_schema_name = dbo

    I do see a logon named "sa" on this system as I do on all my local systems, in fact through comparing this server to my local servers it appears the name "sa" has been replaced with the offending name.

    I have verified the db owner is in fact the db owner that we expect to be, as stated above I have run sp_changeobjectowner (prior to getting down this path) and I have ran the alter schema against all the objects in each of the databases as well.  I've ran multiple different queries from different sources that should identify the objects not owned by the dbo, well modifying the query you sent me explains this offending name is the system "sa" account.

    I am beginning to think  more so that th report is reporting as this offending account owns something that it doesnt simply because the offending account name is the "sa" on this box at least for principal_id 1. Have you ever seen such a situation?

    I'm confused as can be at this point.  I think I will drop the databases completely and recreate them from scratch and have them rescan the db's, my hunch is they will return the same results.  Any thoughts.

    BTW, I truly appreciate your assistance.

    Friday, December 21, 2012 12:43 PM
  • SQL Server permits you to rename the sa account if you feel that this increases your security, and it appears that this has happened on this system.

    Rest assured, principal_id 1 in sys.server_principals with sid 0x01 is sa, even if it is called Frenchie, Jill or Gibberish.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, December 21, 2012 1:26 PM
  • You have been a tremendous help, and if I recall correctly the SA account sorta owns everything on the server as well. 
    Friday, December 21, 2012 3:19 PM