RE: A true lazy question - and clarification -Correction

Am I missing something?

If you run db1..sp_dropuser from the master database, first the procedure
does not exist in the DB and second, if it did, the work would still be
performed against the master database.

To perform the work:
1.Create a script (dropusers.sql) to do what you want against any databases
you want....

use DB1
go
EXEC sp_dropuser cgi_client
EXEC sp_dropuser client_asp

use db2
go
EXEC sp_dropuser cgi_client
EXEC sp_dropuser client_asp

Then
create a CMD job to run the script.
osql -E -ic:\xyz\dropusers.sql (and any other switches you want like; see
BOL for command line switches)

Joshua Morrison MCSE/MCDBA
ConocoPhillips IT SQL DBA
602-728-4629
602-402-6157 Cell

> -----Original Message-----
> From: Patrick Gallucci [SMTP:[Email Address Removed] Sent: Wednesday, January 29, 2003 2:06 PM
> To: LazyDBA.com Discussion
> Subject: RE: A true lazy question - and clarification -Correction
>
> Or this...
>
>
> BEGIN TRANSACTION
> DECLARE @JobID BINARY(16)
> DECLARE @ReturnCode INT
> SELECT @ReturnCode = 0
> IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name =
> N'[Uncategorized (Local)]') < 1
> EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
>
> -- Delete the job with the same name (if it exists)
> SELECT @JobID = job_id
> FROM msdb.dbo.sysjobs
> WHERE (name = N'TEST001')
> IF (@JobID IS NOT NULL)
> BEGIN
> -- Check if the job is a multi-server job
> IF (EXISTS (SELECT *
> FROM msdb.dbo.sysjobservers
> WHERE (job_id = @JobID) AND (server_id <> 0)))
> BEGIN
> -- There is, so abort the script
> RAISERROR (N'Unable to import job ''TEST001'' since there is already
> a multi-server job with this name.', 16, 1)
> GOTO QuitWithRollback
> END
> ELSE
> -- Delete the [local] job
> EXECUTE msdb.dbo.sp_delete_job @job_name = N'TEST001'
> SELECT @JobID = NULL
> END
>
> BEGIN
>
> -- Add the job
> EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT ,
> @job_name = N'TEST001', @owner_login_name = N'sa', @description = N'Test
> DB', @category_name = N'[Uncategorized (Local)]', @enabled = 1,
> @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend =
> 0, @notify_level_eventlog = 0, @delete_level= 0
> IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
>
> -- Add the job steps
> EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
> @step_id = 1, @step_name = N'TESTDB', @command = N'PUBS..sp_adduser
> cgi_client
> GO
> PUBS..sp_adduser client_asp
> GO
> PUBS..sp_dropuser cgi_client
> GO
> PUBS..sp_dropuser client_asp
> GO
> northwind..sp_adduser cgi_client
> GO
> northwind..sp_adduser client_asp
> GO
> northwind..sp_dropuser cgi_client
> GO
> northwind..sp_dropuser client_asp
> GO
> ', @database_name = N'master', @server = N'', @database_user_name = N'',
> @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0,
> @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'',
> @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0,
> @on_fail_action = 2
> IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
> EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID,
> @start_step_id = 1
>
> IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
>
> -- Add the Target Servers
> EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID,
> @server_name = N'(local)'
> IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
>
> END
> COMMIT TRANSACTION
> GOTO EndSave
> QuitWithRollback:
> IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
> EndSave:
>
>
>
> -----Original Message-----
> From: [Email Address Removed] [mailto:[Email Address Removed]
> Sent: Wednesday, January 29, 2003 2:59 PM
> To: LazyDBA.com Discussion
> Subject: RE: A true lazy question - and clarification -Correction
>
>
>
>
> try this...
>
> begin
> DB1..sp_dropuser cgi_client
> end
> begin
> DB1..sp_dropuser client_asp
> end
> begin
> Db2..sp_dropuser cgi_client
> end
> begin
> Db2..sp_dropuser client_asp
> end
>
>
>
> Thanks,
> Lisa Greetham
> Database Administrator, Corporate Internet Group
> Bank One
>
>
>
>
> Ken Nickels <[Email Address Removed] 01/29/2003 03:43 PM
>
> To
> "LazyDBA.com Discussion" <[Email Address Removed] cc
>
> Subject
> RE: A true lazy question - and clarification -Correction
>
>
>
>
>
>
> Hey now... shouldn't this sort of help be reserved for Friday
> afternoons?
>
> -----Original Message-----
> From: Patrick Gallucci [mailto:[Email Address Removed] Sent: Wednesday, January 29, 2003 3:50 PM
> To: LazyDBA.com Discussion
> Subject: RE: A true lazy question - and clarification -Correction
>
>
> Sorry
>
> DB1..sp_dropuser cgi_client
> GO
> DB1..sp_dropuser client_asp
> GO
> Db2..sp_dropuser cgi_client
> GO
> Db2..sp_dropuser client_asp
> GO
> -----Original Message-----
> From: Patrick Gallucci [mailto:[Email Address Removed] Sent: Wednesday, January 29, 2003 2:41 PM
> To: LazyDBA.com Discussion
> Subject: RE: A true lazy question - and clarification
>
>
>
> DB1..sp_dropuser cgi_client
> DB1..sp_dropuser client_asp
> Db2..sp_dropuser cgi_client
> Db2..sp_dropuser client_asp
>
>
>
> -----Original Message-----
> From: [Email Address Removed] [mailto:[Email Address Removed] Sent: Wednesday, January 29, 2003 2:23 PM
> To: LazyDBA.com Discussion
> Subject: RE: A true lazy question - and clarification
>
>
>
>
> I don't think you can change databases in a job step. I would suggest
> creating a step for each database that you need to execute code in.
>
> Thanks,
> Lisa Greetham
> Database Administrator, Corporate Internet Group
> Bank One
>
>
>
>
> Ken Nickels <[Email Address Removed] 01/29/2003 02:50 PM
>
> To
> "LazyDBA.com Discussion" <[Email Address Removed] cc
>
> Subject
> RE: A true lazy question - and clarification
>
>
>
>
>
>
> Just to clariy
>
> If I have a job step with
>
> use DB1
> go
> sp_dropuser cgi_client
> sp_dropuser client_asp
>
> use db2
> go
> sp_dropuser cgi_client
> sp_dropuser client_asp
>
> you're saying I can replace it with...
>
> Begin Tran
> use db1
> commit
> sp_dropuser cgi_client
> sp_dropuser client_asp
>
>
> begin tran
> use Db2
> commit
> sp_dropuser cgi_client
> sp_dropuser client_asp
>
>
> -----Original Message-----
> From: [Email Address Removed] [mailto:[Email Address Removed] Sent: Wednesday, January 29, 2003 1:56 PM
> To: Ken Nickels
> Cc: LazyDBA.com Discussion
> Subject: Re: A true lazy question
>
>
>
>
> Just do the same thing you would have to do in a stored proc. Wrap the
> statement in a begin tran and commit. instead of putting a go at the
> end.
>
> Thanks,
> Lisa Greetham
> Database Administrator, Corporate Internet Group
> Bank One
>
>
>
>
> Ken Nickels <[Email Address Removed] 01/29/2003 01:37 PM
>
> To
> "LazyDBA.com Discussion" <[Email Address Removed] cc
>
> Subject
> A true lazy question
>
>
>
>
>
>
> What do you all do when you put Isql scripts with "go" statements into
> the Agent job scheduler. It usually compains about this.
>
> Thanks
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
> unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional
> commands, e-mail: mssqldba-[Email Address Removed]
>
>
>
>
> This transmission may contain information that is privileged,
> confidential and/or exempt from disclosure under applicable law. If you
> are not the intended recipient, you are hereby notified that any
> disclosure, copying, distribution, or use of the information contained
> herein (including any reliance thereon) is STRICTLY PROHIBITED. If you
> received this transmission in error, please immediately contact the
> sender and destroy the material in its entirety, whether in electronic
> or hard copy format. Thank you.
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
> unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional
> commands, e-mail: mssqldba-[Email Address Removed]
>
>
>
>
> This transmission may contain information that is privileged,
> confidential and/or exempt from disclosure under applicable law. If you
> are not the intended recipient, you are hereby notified that any
> disclosure, copying, distribution, or use of the information contained
> herein (including any reliance thereon) is STRICTLY PROHIBITED. If you
> received this transmission in error, please immediately contact the
> sender and destroy the material in its entirety, whether in electronic
> or hard copy format. Thank you.
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
> unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional
> commands, e-mail: mssqldba-[Email Address Removed]
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
> unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional
> commands, e-mail: mssqldba-[Email Address Removed]
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
> unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional
> commands, e-mail: mssqldba-[Email Address Removed]
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
> unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional
> commands, e-mail: mssqldba-[Email Address Removed]
>
>
>
>
> This transmission may contain information that is privileged,
> confidential and/or exempt from disclosure under applicable law. If you
> are not the intended recipient, you are hereby notified that any
> disclosure, copying, distribution, or use of the information contained
> herein (including any reliance thereon) is STRICTLY PROHIBITED. If you
> received this transmission in error, please immediately contact the
> sender and destroy the material in its entirety, whether in electronic
> or hard copy format. Thank you.
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
> unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional
> commands, e-mail: mssqldba-[Email Address Removed]
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional commands, e-mail: mssqldba-[Email Address Removed]MS Sql Server LazyDBA home page