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?
>>
>>
>

Running .Sql Files in a batch

Hi,
I have got multiple .sql files(text files) having T-SQL Statements. I want
to run them all together from query analyzer...how can i do that...
Your help will be appreciatedhI
Let me say you have a file as
Use Pubs
GO
CREATE PROCEDURE spMyProc
AS
SELECT * FROM Titles
--Run this command in QA
EXEC master..xp_cmdshell 'osql.exe -S SERVERNAME -U sa -P pass -i
"D:\ORD1.sql"'
"kailux4" <kailux4@.discussions.microsoft.com> wrote in message
news:067CC39F-41E2-4170-B132-927FDB458C9C@.microsoft.com...
> Hi,
> I have got multiple .sql files(text files) having T-SQL Statements. I want
> to run them all together from query analyzer...how can i do that...
> Your help will be appreciated|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eS%23FL1oaFHA.2900@.TK2MSFTNGP15.phx.gbl...
> hI
> Let me say you have a file as
> Use Pubs
> GO
> CREATE PROCEDURE spMyProc
> AS
> SELECT * FROM Titles
> --Run this command in QA
> EXEC master..xp_cmdshell 'osql.exe -S SERVERNAME -U sa -P pass -i
> "D:\ORD1.sql"'
But bear in mind that "D:\" is a drive on the __server__, not on the client.
Regards
Wojtek|||"kailux4" <kailux4@.discussions.microsoft.com> wrote in message
news:067CC39F-41E2-4170-B132-927FDB458C9C@.microsoft.com...
> Hi,
> I have got multiple .sql files(text files) having T-SQL Statements. I want
> to run them all together from query analyzer...how can i do that...
> Your help will be appreciated
Why from QA? Why not write a batch and use isql.exe within?
Regards
Wojtek|||Hi,
I recommend you to run the .SQL files from command prompt using OSQL
redirecting the output to a log file.
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name.sql >
output.log
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name1.sql >>
output.log
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name2.sql >>
output.log
Thanks
Hari
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eS%23FL1oaFHA.2900@.TK2MSFTNGP15.phx.gbl...
> hI
> Let me say you have a file as
> Use Pubs
> GO
> CREATE PROCEDURE spMyProc
> AS
> SELECT * FROM Titles
> --Run this command in QA
> EXEC master..xp_cmdshell 'osql.exe -S SERVERNAME -U sa -P pass -i
> "D:\ORD1.sql"'
>
> "kailux4" <kailux4@.discussions.microsoft.com> wrote in message
> news:067CC39F-41E2-4170-B132-927FDB458C9C@.microsoft.com...
>> Hi,
>> I have got multiple .sql files(text files) having T-SQL Statements. I
>> want
>> to run them all together from query analyzer...how can i do that...
>> Your help will be appreciated
>

Running .Sql Files in a batch

Hi,
I have got multiple .sql files(text files) having T-SQL Statements. I want
to run them all together from query analyzer...how can i do that...
Your help will be appreciated
hI
Let me say you have a file as
Use Pubs
GO
CREATE PROCEDURE spMyProc
AS
SELECT * FROM Titles
--Run this command in QA
EXEC master..xp_cmdshell 'osql.exe -S SERVERNAME -U sa -P pass -i
"D:\ORD1.sql"'
"kailux4" <kailux4@.discussions.microsoft.com> wrote in message
news:067CC39F-41E2-4170-B132-927FDB458C9C@.microsoft.com...
> Hi,
> I have got multiple .sql files(text files) having T-SQL Statements. I want
> to run them all together from query analyzer...how can i do that...
> Your help will be appreciated
|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eS%23FL1oaFHA.2900@.TK2MSFTNGP15.phx.gbl...
> hI
> Let me say you have a file as
> Use Pubs
> GO
> CREATE PROCEDURE spMyProc
> AS
> SELECT * FROM Titles
> --Run this command in QA
> EXEC master..xp_cmdshell 'osql.exe -S SERVERNAME -U sa -P pass -i
> "D:\ORD1.sql"'
But bear in mind that "D:\" is a drive on the __server__, not on the client.
Regards
Wojtek
|||"kailux4" <kailux4@.discussions.microsoft.com> wrote in message
news:067CC39F-41E2-4170-B132-927FDB458C9C@.microsoft.com...
> Hi,
> I have got multiple .sql files(text files) having T-SQL Statements. I want
> to run them all together from query analyzer...how can i do that...
> Your help will be appreciated
Why from QA? Why not write a batch and use isql.exe within?
Regards
Wojtek
|||Hi,
I recommend you to run the .SQL files from command prompt using OSQL
redirecting the output to a log file.
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name.sql >
output.log
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name1.sql >>
output.log
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name2.sql >>
output.log
Thanks
Hari
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eS%23FL1oaFHA.2900@.TK2MSFTNGP15.phx.gbl...
> hI
> Let me say you have a file as
> Use Pubs
> GO
> CREATE PROCEDURE spMyProc
> AS
> SELECT * FROM Titles
> --Run this command in QA
> EXEC master..xp_cmdshell 'osql.exe -S SERVERNAME -U sa -P pass -i
> "D:\ORD1.sql"'
>
> "kailux4" <kailux4@.discussions.microsoft.com> wrote in message
> news:067CC39F-41E2-4170-B132-927FDB458C9C@.microsoft.com...
>
sql

Running .Sql Files in a batch

Hi,
I have got multiple .sql files(text files) having T-SQL Statements. I want
to run them all together from query analyzer...how can i do that...
Your help will be appreciatedhI
Let me say you have a file as
Use Pubs
GO
CREATE PROCEDURE spMyProc
AS
SELECT * FROM Titles
--Run this command in QA
EXEC master..xp_cmdshell 'osql.exe -S SERVERNAME -U sa -P pass -i
"D:\ORD1.sql"'
"kailux4" <kailux4@.discussions.microsoft.com> wrote in message
news:067CC39F-41E2-4170-B132-927FDB458C9C@.microsoft.com...
> Hi,
> I have got multiple .sql files(text files) having T-SQL Statements. I want
> to run them all together from query analyzer...how can i do that...
> Your help will be appreciated|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eS%23FL1oaFHA.2900@.TK2MSFTNGP15.phx.gbl...
> hI
> Let me say you have a file as
> Use Pubs
> GO
> CREATE PROCEDURE spMyProc
> AS
> SELECT * FROM Titles
> --Run this command in QA
> EXEC master..xp_cmdshell 'osql.exe -S SERVERNAME -U sa -P pass -i
> "D:\ORD1.sql"'
But bear in mind that "D:\" is a drive on the __server__, not on the client.
Regards
Wojtek|||"kailux4" <kailux4@.discussions.microsoft.com> wrote in message
news:067CC39F-41E2-4170-B132-927FDB458C9C@.microsoft.com...
> Hi,
> I have got multiple .sql files(text files) having T-SQL Statements. I want
> to run them all together from query analyzer...how can i do that...
> Your help will be appreciated
Why from QA? Why not write a batch and use isql.exe within?
Regards
Wojtek|||Hi,
I recommend you to run the .SQL files from command prompt using OSQL
redirecting the output to a log file.
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name.sql >
output.log
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name1.sql >>
output.log
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name2.sql >>
output.log
Thanks
Hari
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eS%23FL1oaFHA.2900@.TK2MSFTNGP15.phx.gbl...
> hI
> Let me say you have a file as
> Use Pubs
> GO
> CREATE PROCEDURE spMyProc
> AS
> SELECT * FROM Titles
> --Run this command in QA
> EXEC master..xp_cmdshell 'osql.exe -S SERVERNAME -U sa -P pass -i
> "D:\ORD1.sql"'
>
> "kailux4" <kailux4@.discussions.microsoft.com> wrote in message
> news:067CC39F-41E2-4170-B132-927FDB458C9C@.microsoft.com...
>

Run SQL T-SQL Job Step across Different Domains

I have two sql 2005 servers in two different domains. I have a SQL job on
one of the servers that runs a T-SQL script that accesses databases on the
SQL box inthe other domain.
I'm getting a message in the job history that the job won't run because of
the security context.
How can I set this up?
Thanks for your help.
--
MWDWhat Operating Systems are these two servers running?
What trusts, if any, are set up between the two domains?
How are you trying to connect?
What is the exact error number and error message?
-Sue
On Thu, 9 Aug 2007 08:10:09 -0700, MWD
<MWD@.discussions.microsoft.com> wrote:

>I have two sql 2005 servers in two different domains. I have a SQL job on
>one of the servers that runs a T-SQL script that accesses databases on the
>SQL box inthe other domain.
>I'm getting a message in the job history that the job won't run because of
>the security context.
>How can I set this up?
>Thanks for your help.sql

Run Report with specific Records.

I am not sure where i should post this question since it falls both in Report Server and T-Sql but here goes...

I currently need to run a Report that has only specified records that the client/user wants by clicking the check in the check box next to the record they want. They can pick as many or a few of the records that want then run a report only with the records they indicated they wanted... i am thinking they will need some kind of t-sql statement either a function or temp table but i am not sure if even that...

if anyone has any ideas please reply...

Thanks,
WoFe

EXAMPLE: Instead of running a report on records 1, 2, 3, 4, 5, 6, 7, 8, 9
they would run the report on records: 2, 5, 6, 9

You need to use a multivalue parameter. There is a tutorial here
http://msdn2.microsoft.com/en-us/library/aa337432.aspx

Run dynamic SQL in debugger, get sql dump

Hi,
One of our developer runs a stored procedure containing dynamic SQL in
T-SQL Debugger. She consistently gets error ( see below ):
SqlDumpExceptionHandler: Process 73 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
If she runs it without debugger, it runs fine. She checks the code many
times and can't find anything wrong in the code. We have no idea what's
wrong.
The server is a SQL Server 2000 (SP3) on a Window 2000 with service pack
4.
Thank you.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!If memory serves there was a bug with the debugger where
if you run it from the client machine it would either not
work, or result in an error (sorry can't find the
Microsoft Page).
How I solved this is to run the debugger on the server.
One of the Great Ones, Uri, Narayana, Wayne, Dejan, Tibor,
Dan, Andrew or Adam could probably tell you the reason and
the fix.
Peter
"I favor the Civil Rights Act of 1964 and it must be
enforced at gunpoint if necessary."
Ronald Reagan
>--Original Message--
>
>Hi,
>One of our developer runs a stored procedure containing
dynamic SQL in
>T-SQL Debugger. She consistently gets error ( see below ):
>SqlDumpExceptionHandler: Process 73 generated fatal
exception c0000005
>EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating
this process.
>If she runs it without debugger, it runs fine. She checks
the code many
>times and can't find anything wrong in the code. We have
no idea what's
>wrong.
>The server is a SQL Server 2000 (SP3) on a Window 2000
with service pack
>4.
>Thank you.
>
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>|||and Jacco, certinly must not forget Jacco...
Peter
>--Original Message--
>If memory serves there was a bug with the debugger where
>if you run it from the client machine it would either not
>work, or result in an error (sorry can't find the
>Microsoft Page).
>How I solved this is to run the debugger on the server.
>One of the Great Ones, Uri, Narayana, Wayne, Dejan,
Tibor,
>Dan, Andrew or Adam could probably tell you the reason
and
>the fix.
>Peter
>"I favor the Civil Rights Act of 1964 and it must be
>enforced at gunpoint if necessary."
>Ronald Reagan
>
>
>
>>--Original Message--
>>
>>Hi,
>>One of our developer runs a stored procedure containing
>dynamic SQL in
>>T-SQL Debugger. She consistently gets error ( see
below ):
>>SqlDumpExceptionHandler: Process 73 generated fatal
>exception c0000005
>>EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating
>this process.
>>If she runs it without debugger, it runs fine. She
checks
>the code many
>>times and can't find anything wrong in the code. We have
>no idea what's
>>wrong.
>>The server is a SQL Server 2000 (SP3) on a Window 2000
>with service pack
>>4.
>>Thank you.
>>
>>*** Sent via Developersdex http://www.developersdex.com
>***
>>Don't just participate in USENET...get rewarded for it!
>>.
>.
>|||Did you Google for an answer? I searched for below keywords:
c0000005 EXCEPTION_ACCESS_VIOLATION debug
And got some 100 matches. One plausible is http://support.microsoft.com/default.aspx?scid=kb;EN-US;270061.
If you can't find a KB that applies to you, and you are current on service pack, you need to open a case with
MS, since access violations (etc) are most often bugs in SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yanjie Ren" <yren@.cc3.com> wrote in message news:%23lZEgg3iEHA.396@.TK2MSFTNGP12.phx.gbl...
>
> Hi,
> One of our developer runs a stored procedure containing dynamic SQL in
> T-SQL Debugger. She consistently gets error ( see below ):
> SqlDumpExceptionHandler: Process 73 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> If she runs it without debugger, it runs fine. She checks the code many
> times and can't find anything wrong in the code. We have no idea what's
> wrong.
> The server is a SQL Server 2000 (SP3) on a Window 2000 with service pack
> 4.
> Thank you.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

RULES and custom defaults in Express edition?

Does it allow you it create rules ? a right click does not present an option, as if you cannot. Going to try T-SQL to see if that works.

John

JohnF wrote:

Does it allow you it create rules ? a right click does not present an option, as if you cannot. Going to try T-SQL to see if that works.

John

No, but only when you want it to make up rules,|||

I see CHECK constraints is the way to go now

|||

Yep,

see ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/63e1bd15-d618-4ee4-a819-641137101816.htm or http://msdn2.microsoft.com/en-us/library/ms187615.aspx, they are deprecated.

Jens K. Suessmeyer.

http://www.sqlserver2005.de