Professional Web Applications Themes

Will anyone know this? - Microsoft SQL / MS SQL Server

Hi, Does anybody know how to get enterprise manager to stop putting lines like setuser('taylorb') in the output .sql file when you ask it to script a whole load of stored procedures? I had even created a VB program to remove these lines but it isn't working properly now, and I thought it would be the sort of thing you could turn off but not in an obvious way. It doesn't work when I'm trying to set the database up on another server for example because there's some security issue with setuser (I'm not an administrator but am the database ...

  1. #1

    Default Will anyone know this?

    Hi,
    Does anybody know how to get enterprise manager to stop
    putting lines like setuser('taylorb') in the output .sql
    file when you ask it to script a whole load of stored
    procedures?
    I had even created a VB program to remove these lines but
    it isn't working properly now, and I thought it would be
    the sort of thing you could turn off but not in an obvious
    way. It doesn't work when I'm trying to set the database
    up on another server for example because there's some
    security issue with setuser (I'm not an administrator but
    am the database owner) - I just want to remove setuser
    calls.
    Anybody know?
    Ben Guest

  2. #2

    Default Re: Will anyone know this?

    That's right, they're not dbo owned they're taylorb owned -
    but I am set up to be the 'dbowner' - there's intense
    confusion here! Am I the database owner, or is the sa
    account the database owner, and what is the difference
    between 'dbo' and 'dbowner'?

    It's only ever going to be me running the scripts, or if
    it is someone else, it's likely to be because they've
    taken over the database and in that case they will have
    been made the owner of it and will be running it under
    their login, so setuser will still be inappropriate.
    I understand why it's doing this, but presumably there
    isn't any way to decide for myself and turn it off, or do
    I have to manually get rid of it?

    I can't see what its actual purpose is, because if someone
    else wanted to create the tables 'as me', wouldn't they
    need to know my password, and if not isn't this an un-
    security conscious function you could use to get round
    user-restrictions?

    Thanks
    Ben

     
    have objects that 
    that user during the 
    risk of the 
    do remove it make 
    owners. [/ref]
    but [/ref]
    obvious [/ref]
    but 
    >
    >
    >.
    >[/ref]
    Ben Guest

  3. #3

    Default Re: Will anyone know this?

    Ben,

    Is there any particular reason why they are owned by you (taylorb)? Why
    not make them all dbo and the issue goes away. dbowner is a role of which
    you have been assigned for that db but dbo is the actual object owner when
    it is not owned by any other particular user. It will never be owned by sa,
    it will be dbo instead. If you own all the objects (taylorb) and you leave
    that company thenit may be an issue for the next person to manage these
    objects if they can't get access to your account. If these are dbo then
    there is no problems.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Ben Taylor" <co.uk> wrote in message
    news:0ab401c34d30$4f0d8180$gbl... 
    > have objects that 
    > that user during the 
    > risk of the 
    > do remove it make 
    > owners. [/ref]
    > but [/ref]
    > obvious [/ref]
    > but 
    > >
    > >
    > >.
    > >[/ref][/ref]


    Andrew Guest

  4. #4

    Default Re: Will anyone know this?

    You can determine if your login is the database owner with sp_helpdb.
    If your login isn't listed as the owner, you are probably a member of
    the db_owner fixed database role instead. You can execute
    sp_helprolemember 'db_owner' to see if this is the case.

    The only required user in a SQL Server database is dbo and this special
    user is mapped to the login specified as the database owner. The dbo
    user has full control over the database and can manipulate objects in
    any schema in the database. Members of the db_owner role have similar
    permissions to the dbo user except that their default schema is their
    user name instead of dbo. Consequently, these users must qualify the
    object name with the desired owner in order to create objects owned by
    other users.

    As Andrew mentioned, dbo object ownership is a best practice in SQL
    Server unless you have a specific reason to do otherwise. This
    eliminates a number of issues with transferring schema to other
    databases or servers and facilitates unbroken ownership chains. When an
    existing object is referenced with an unqualified name, SQL Server first
    checks for an object owned by the current user and uses the dbo-owned
    object only if the user-owned object does no exist. This behavior is
    regardless of role membership. One of the nice things about having all
    objects owned by dbo is that the referenced object is unambiguous
    regardless of whether or not the object name is qualified.

    BTW, the reason for the SETUSER in the generated scripts for textual
    objects (stored procedure, views, triggers and functions) is to ensure
    unambiguous behavior in case the object text does not contain the owner.
    If you need to script non-dbo objects and the object names are qualified
    in the object text, you might consider using SQLDMO directly from your
    VB program rather than using EM. Sample VBScript below.

    Option Explicit
    Dim oFSO, oFile
    Dim oSQLServer, oDatabase, oStoredProcedure
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFile = oFSO.OpenTextFile("C:\Scripts\MyProcs.sql", 2, True)
    Set oSQLServer = CreateObject("SQLDMO.SQLServer")
    oSQLServer.LoginSecure = True
    oSQLServer.Connect("MyServer")
    Set oDatabase = oSQLServer.Databases("MyDatabase")
    For Each oStoredProcedure In oDatabase.StoredProcedures
    oFile.WriteLine oStoredProcedure.Script
    oFile.WriteLine "GO"
    Next
    oFile.Close


    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    -----------------------
    SQL FAQ links (courtesy Neil Pike):

    http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
    http://www.sqlserverfaq.com
    http://www.mssqlserver.com/faq
    -----------------------

    "Ben Taylor" <co.uk> wrote in message
    news:0ab401c34d30$4f0d8180$gbl... 
    > have objects that 
    > that user during the 
    > risk of the 
    > do remove it make 
    > owners. [/ref]
    > but [/ref]
    > obvious [/ref]
    > but 
    > >
    > >
    > >.
    > >[/ref][/ref]


    Dan Guest

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139