Query dbowner

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • paulwragg2323@hotmail.com
    #1

    Query dbowner

    Hi

    We have a script that allows customers to change the users of the
    database including the database owner. This script can be run at any
    time. However, sometimes (and it really is only sometimes!) when the
    following statement executes:

    "exec sp_changedbowne r @USER_OWNER"

    the following error is reported:

    "The proposed new database owner is already a user in the database."

    I have checked the other postings and the Books Online but cannot find
    a way to query the current name of the dbowner i.e. the value of
    @USER_OWNER above. We have to be able to do this within the script so
    that if the @USER_OWNER variable is already the dbowner we do not
    bother to execute the statement as otherwise this could display the
    error. Can anybody help?

    Thanks in Advance

    Paul

    Tags: None
    • Danny
      #2
      Re: Query dbowner

      EM gets it from exec sp_MSdbuseracce ss N'db', N'%'

      which calls

      select o.name, o.version, o.crdate, suser_sname(o.s id), o.dbid, o.status,
      o.category, o.status2, DatabasePropert y(o.name, N'isfulltextena bled')
      from master.dbo.sysd atabases o where o.name = @qual

      so

      select suser_sname(o.s id) from master.dbo.sysd atabases where name = 'DBNAME'
      should do it.

      Danny

      <paulwragg2323@ hotmail.com> wrote in message
      news:1102498820 .367939.212520@ f14g2000cwb.goo glegroups.com.. .[color=blue]
      > Hi
      >
      > We have a script that allows customers to change the users of the
      > database including the database owner. This script can be run at any
      > time. However, sometimes (and it really is only sometimes!) when the
      > following statement executes:
      >
      > "exec sp_changedbowne r @USER_OWNER"
      >
      > the following error is reported:
      >
      > "The proposed new database owner is already a user in the database."
      >
      > I have checked the other postings and the Books Online but cannot find
      > a way to query the current name of the dbowner i.e. the value of
      > @USER_OWNER above. We have to be able to do this within the script so
      > that if the @USER_OWNER variable is already the dbowner we do not
      > bother to execute the statement as otherwise this could display the
      > error. Can anybody help?
      >
      > Thanks in Advance
      >
      > Paul
      >[/color]


        Comment

        • Paul
          #3
          Re: Query dbowner

          This works fine (except missing o for alias for
          master.dbo.sysd atabases) and is just what I needed.

          Thanks Danny.

            Comment

            • Paul
              #4
              Re: Query dbowner

              This works fine (except missing o for alias for
              master.dbo.sysd atabases) and is just what I needed.

              Thanks Danny.

                Comment

                • Dan Guzman
                  #5
                  Re: Query dbowner

                  One method to retrieve the current database owner:

                  DECLARE @CurrentOwner nvarchar(256)
                  SELECT @CurrentOwner = SUSER_SNAME(sid )
                  FROM master..sysdata bases
                  WHERE name = DB_NAME()

                  You can then conditionally change the owner:

                  IF @CurrentOwner <> @USER_OWNER
                  EXEC sp_changedbowne r @USER_OWNER

                  However, your error message is most likely because the new database owner is
                  a current database user rather than the database owner. The query below
                  will identify this condition as well as the database owner.

                  SELECT
                  l.name as LoginName,
                  u.name AS UserName
                  FROM master..syslogi ns l
                  JOIN sysusers u ON u.sid = l.sid
                  WHERE l.sid = SUSER_SID(@USER _OWNER)

                  --
                  Hope this helps.

                  Dan Guzman
                  SQL Server MVP

                  <paulwragg2323@ hotmail.com> wrote in message
                  news:1102498820 .367939.212520@ f14g2000cwb.goo glegroups.com.. .[color=blue]
                  > Hi
                  >
                  > We have a script that allows customers to change the users of the
                  > database including the database owner. This script can be run at any
                  > time. However, sometimes (and it really is only sometimes!) when the
                  > following statement executes:
                  >
                  > "exec sp_changedbowne r @USER_OWNER"
                  >
                  > the following error is reported:
                  >
                  > "The proposed new database owner is already a user in the database."
                  >
                  > I have checked the other postings and the Books Online but cannot find
                  > a way to query the current name of the dbowner i.e. the value of
                  > @USER_OWNER above. We have to be able to do this within the script so
                  > that if the @USER_OWNER variable is already the dbowner we do not
                  > bother to execute the statement as otherwise this could display the
                  > error. Can anybody help?
                  >
                  > Thanks in Advance
                  >
                  > Paul
                  >[/color]


                    Comment

                    • Paul
                      #6
                      Re: Query dbowner

                      Thanks to both of you for replying to my post.
                      I have now solved this and proven that both methods will work.

                      Thanks.

                        Comment

                        Previous template Next
                        Working...