Change connected user to avoid owner prefix in queries

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

    Change connected user to avoid owner prefix in queries

    Hi everybody,

    I've five instances of SQL Server 2000 with the SAME database with a
    DIFFERENT owner in each server. I, as the administrator, have a lot of
    queries that I have to execute in some or all servers. The problem is
    that I have to connect to all servers with MY user, not each of the db
    owners...

    So I have queries this way:

    select * from mike.table1 t1 join mike.table2 t2 on...

    And when I connect to another server I have to change mike for jeremy
    in all the SQLs...

    And when I connect to another server I have to change jeremy for nina
    in all the SQLs...

    I know that there was an old, v7, deprecated way to change the
    "schema", something like

    change current user to kimberly
    go
    select * from table1 t1 join table2 t2 on...

    This way, I'll change ONLY once the connected user. I could even do at
    the beginning of the script an IF, to change the connected user
    depending on @@SERVERNAME !!!

    Can someone remember this instruction???

    Thanks in advance for your help !!!

    Tags: None
    • MADS
      #2
      Re: Change connected user to avoid owner prefix in queries

      Found it !!!

      setuser 'q01'

      -- quien importo una orden de transporte
      select USERNAME, SYDATE, SYTIME, CLIENT
      from TPLOG
      where CMDSTRING like '%D02K909789%
      >From BOL:
      SETUSER
      Allows a member of the sysadmin fixed server role or db_owner fixed
      database role to impersonate another user.

      Important SETUSER is included in Microsoft® SQL Server™ 2000 only for
      backward compatibility, and its usage is not recommended. SETUSER may
      not be supported in a future release of SQL Server.

      Syntax
      SETUSER [ 'username' [ WITH NORESET ] ]

      Arguments
      'username'

      Is the name of a SQL Server or Microsoft Windows NT® user in the
      current database that is impersonated. When username is not specified,
      the original identity of the system administrator or database owner
      impersonating the user is reestablished.

      WITH NORESET

      Specifies that subsequent SETUSER statements (with no specified
      username) do not reset to the system administrator or database owner.

        Comment

        Previous template Next
        Working...