Running as another user

SQL Server 2000
I've been looking for a way to test something as another SQL Server user in
T-SQL (linked servers). The only way I can come up with is something like
this:
EXEC xp_cmdshell('osql -U TestLogin -P password -q "Select * FROM
Server.DB.Owner.Table"')
Seems kinda kludgy. Is this all that's available, or is there a cleaner way?Jay,
If you are testing interactively, then in SQL Server 2000 Query Analyzer you
can login a new query session as TestLogin. (If you always use QA in
Windows Authentication, just click the SQL Authentication radio button, then
enter TestLogin and password.) That would probably be better than running
OSQL scripts.
In SQL Server 2000 the SETUSER command will allow you to imitate another
user if you are sysadmin. But a linked server will not allow SETUSER
credentials through the link, so that is no help to you.
RLF
"Jay" <spam@.nospam.org> wrote in message
news:u5HSQeEAIHA.5184@.TK2MSFTNGP02.phx.gbl...
> SQL Server 2000
> I've been looking for a way to test something as another SQL Server user
> in T-SQL (linked servers). The only way I can come up with is something
> like this:
> EXEC xp_cmdshell('osql -U TestLogin -P password -q "Select * FROM
> Server.DB.Owner.Table"')
> Seems kinda kludgy. Is this all that's available, or is there a cleaner
> way?
>|||You can configure the security information in the linked server
configuration panel.
"Jay" <spam@.nospam.org> wrote in message
news:u5HSQeEAIHA.5184@.TK2MSFTNGP02.phx.gbl...
> SQL Server 2000
> I've been looking for a way to test something as another SQL Server user
> in T-SQL (linked servers). The only way I can come up with is something
> like this:
> EXEC xp_cmdshell('osql -U TestLogin -P password -q "Select * FROM
> Server.DB.Owner.Table"')
> Seems kinda kludgy. Is this all that's available, or is there a cleaner
> way?
>|||> You can configure the security information in the linked server
> configuration panel.
I'm doing this completly in T-SQL so I have scripts to run on all servers in
the farm. The only use of EM and clicking on Linked Servers is to find what
I'm looking for, or resetting things when I screw up.
However, that said, I fail to see how that helps me test the configuration.
>
> "Jay" <spam@.nospam.org> wrote in message
> news:u5HSQeEAIHA.5184@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I've been looking for a way to test something as another SQL Server user
>> in T-SQL (linked servers). The only way I can come up with is something
>> like this:
>> EXEC xp_cmdshell('osql -U TestLogin -P password -q "Select * FROM
>> Server.DB.Owner.Table"')
>> Seems kinda kludgy. Is this all that's available, or is there a cleaner
>> way?
>|||Good ideas, thank you. I will test them shortly.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OW1zakEAIHA.3848@.TK2MSFTNGP05.phx.gbl...
> Jay,
> If you are testing interactively, then in SQL Server 2000 Query Analyzer
> you can login a new query session as TestLogin. (If you always use QA in
> Windows Authentication, just click the SQL Authentication radio button,
> then enter TestLogin and password.) That would probably be better than
> running OSQL scripts.
> In SQL Server 2000 the SETUSER command will allow you to imitate another
> user if you are sysadmin. But a linked server will not allow SETUSER
> credentials through the link, so that is no help to you.
> RLF
> "Jay" <spam@.nospam.org> wrote in message
> news:u5HSQeEAIHA.5184@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I've been looking for a way to test something as another SQL Server user
>> in T-SQL (linked servers). The only way I can come up with is something
>> like this:
>> EXEC xp_cmdshell('osql -U TestLogin -P password -q "Select * FROM
>> Server.DB.Owner.Table"')
>> Seems kinda kludgy. Is this all that's available, or is there a cleaner
>> way?
>|||And, just a FWIW, when you move to 2005, you have this beautiful EXECUTE AS command (which can be
used both at login or user level).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <spam@.nospam.org> wrote in message news:uWKmlsEAIHA.320@.TK2MSFTNGP04.phx.gbl...
> Good ideas, thank you. I will test them shortly.
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:OW1zakEAIHA.3848@.TK2MSFTNGP05.phx.gbl...
>> Jay,
>> If you are testing interactively, then in SQL Server 2000 Query Analyzer you can login a new
>> query session as TestLogin. (If you always use QA in Windows Authentication, just click the SQL
>> Authentication radio button, then enter TestLogin and password.) That would probably be better
>> than running OSQL scripts.
>> In SQL Server 2000 the SETUSER command will allow you to imitate another user if you are
>> sysadmin. But a linked server will not allow SETUSER credentials through the link, so that is no
>> help to you.
>> RLF
>> "Jay" <spam@.nospam.org> wrote in message news:u5HSQeEAIHA.5184@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I've been looking for a way to test something as another SQL Server user in T-SQL (linked
>> servers). The only way I can come up with is something like this:
>> EXEC xp_cmdshell('osql -U TestLogin -P password -q "Select * FROM Server.DB.Owner.Table"')
>> Seems kinda kludgy. Is this all that's available, or is there a cleaner way?
>>
>|||I saw that in the 2005 docs, but ignored it (which was hard to do).
It seems that there is only enough money in the budget to either get a SAN,
or upgrade to 2005. Given that choice, we'll be on 2000 for another 1-4
years and if too much time goes by, we'll be going from 2000, to 2008.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:FD5708A7-D8B8-4D90-8FB7-8AEA8A57D2CA@.microsoft.com...
> And, just a FWIW, when you move to 2005, you have this beautiful EXECUTE
> AS command (which can be used both at login or user level).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <spam@.nospam.org> wrote in message
> news:uWKmlsEAIHA.320@.TK2MSFTNGP04.phx.gbl...
>> Good ideas, thank you. I will test them shortly.
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:OW1zakEAIHA.3848@.TK2MSFTNGP05.phx.gbl...
>> Jay,
>> If you are testing interactively, then in SQL Server 2000 Query Analyzer
>> you can login a new query session as TestLogin. (If you always use QA
>> in Windows Authentication, just click the SQL Authentication radio
>> button, then enter TestLogin and password.) That would probably be
>> better than running OSQL scripts.
>> In SQL Server 2000 the SETUSER command will allow you to imitate another
>> user if you are sysadmin. But a linked server will not allow SETUSER
>> credentials through the link, so that is no help to you.
>> RLF
>> "Jay" <spam@.nospam.org> wrote in message
>> news:u5HSQeEAIHA.5184@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I've been looking for a way to test something as another SQL Server
>> user in T-SQL (linked servers). The only way I can come up with is
>> something like this:
>> EXEC xp_cmdshell('osql -U TestLogin -P password -q "Select * FROM
>> Server.DB.Owner.Table"')
>> Seems kinda kludgy. Is this all that's available, or is there a cleaner
>> way?
>>
>>
>