Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: Default owner for SQL 2k users

    Hi

    Overview - non-sysadmin access to SQL 2k box using 2k5 tools. Not used 2k in over a year,

    Any way to set the default owner for objects created by a user? So if user x runs:

    Code:
     CREATE TABLE mytable
        (
            mycol    BIT
        )
    it is owned by dbo not x (dbo.mytable not x.mytable)? Note I can't (or won't for now anyway) change the DDL above. I also don't believe I can do this with the SSMS tools (schema <> owner). I also can't use sp_adduser...
    So 2k comliant T-SQL or at a push SMO only please!

    Ta
    Testimonial:
    pootle flump
    ur codings are working excelent.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Tricky question; I thought the answer was obvious but I think it's a little more subtle. Anyway, why should it matter; you should be changing your DDL to include the owner/schema.

    My gut reaction is dbo default; but I'll have a play after I finish my sarnie

    And ownership works in a very different way in 2005. There's (little to) no ownership chaining/security inheritance in 2K, so I'm not really sure what detail you're trying to pick at at the moment.
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Problem is it ain't my DDL. I can go changing it however that then means my DML needs the owner prefix too to be efficient - otherwise the optimser looks for objects owned by "x" first. The DML is not mine either....

    There isn't any such thing as ownership in 2k5. The schema concept maps much more closely to the ANSI SQL "domain" AFAIK and I use it like crazy in 2k5. The "owner" concept in 2k just seems inherently flawed to me and I don't like anything being owned by anything other than dbo.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So is your question closer to
    Code:
    CREATE TABLE gv.gv (gv bit)
    Execute as gv
    Code:
    SELECT * FROM gv
    In which case it looks for any objects owned by gv before proceeding to dbo (the default owner)
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Now, now, Pootle. What did your mother tell you about sharing?

    The way to do this is to set the login as the database owner. Look up sp_changedbowner. If you need more than one such login (and this had better be development only), then you can look into creating aliases to DBO. (sp_addalias, if I remember).

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think, WD 24601, you've made me realise I'm still thinking in 2k5 schema terms.

    So only one login (and we are talking logins not users right?) can be the database owner and default to dbo ownership? (Apart from the sysadmins and a few other server roles - I know they map to dbo).

    Normally I create the stuff myself as sysadmin and then create logins to assecc the sprocs. In this case, a third party app, the app creates the database objects using a SQL login mapped to a database user. Once created I bump the user permissions right down so it has no DDL rights.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    So is your question closer to
    Code:
    CREATE TABLE gv.gv (gv bit)
    Execute as gv
    Code:
    SELECT * FROM gv
    In which case it looks for any objects owned by gv before proceeding to dbo (the default owner)
    That looks like a statement George. What's my question again?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think I've just learnt that I've forgotten more about 2k than I remember. That took all of 14 months. Oh crap.

    I want a user to, by default, use "dbo" but not be a database owner or member of db_owner because it should not have DDL rights. Is this a contradiction in terms as far as 2k database architecture goes?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    you can look into adding the user to the db_ddladmin group, but I believe the user's objects will not default to dbo, unless told. Remember. Third party vendors rarely expect a customer to apply any actual security to their application.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah I'm used to having full control of the box & SQL code too. Usually with stuff I've built myself or by the team. Our in house coding standards insist on two part naming conventions so I usually don't give default shemas\ 2k equivalent a second thought.


    I don't want to elevate this users privleges any further - I actually plan to start hacking them back to least required. I expect the app will work just fine as is.

    Ta for the help
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You could kludge this by running this stored procedure every minute or so. It isn't perfect, but it is the closest that I can get on short notice.
    Code:
    --  ptp  20080311  See http://www.dbforums.com/showthread.php?t=1628192
    
    CREATE PROCEDURE pPootleKludge2K
    AS
    
    DECLARE
       @cOld			NVARCHAR(776)
    ,  @cOwner			sysname
    ,  @cTable			sysname
    
    DECLARE zPootleKludge2K CURSOR FOR SELECT
       a.TABLE_SCHEMA, a.TABLE_NAME					-- Fetch owner and table
       FROM INFORMATION_SCHEMA.TABLES AS a
       WHERE NOT EXISTS (SELECT *					-- Only if safe to rename
          FROM INFORMATION_SCHEMA.TABLES AS b
          WHERE  b.TABLE_NAME = a.TABLE_NAME		-- Table matches, but...
             AND b.TABLE_SCHEMA != a.TABLE_SCHEMA)	-- ...owner does not
    
    OPEN CURSOR zPootleKludge2K
    FETCH zPootleKludge2K INTO @cOwner, @cTable
    
    WHILE 0 = @@fetch_status
       BEGIN
          SET @cOld = QuoteName(@cOwner) + '.' + QuoteName(@cTable)
          EXECUTE sp_changeobjectowner @cOld, 'dbo'
    
          FETCH zPootleKludge2K INTO @cOwner, @cTable
       END
    
    CLOSE zPootleKludge2K
    DEALLOCATE zPootleKludge2K
    
    RETURN
    -PatP

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thank you Pat

    I think I am going to go with the flow rather than swim against it. It seems that I have returned to SQL 2K with some preconceptions on how things should be done based on my last 16 months working with 2k5.

    Appreciated fella
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •