That works better. Thanks. (I hate all those ' , '', ", ''', etc. ) 

Bill

Shawn,

I modified your code as follows:

>>>>

EXEC master.dbo.xp_execresultset '

SELECT

        ''GRANT EXECUTE ON dbo.'' + SPECIFIC_NAME + '' TO Maintenance

FROM

        INFORMATION_SCHEMA.ROUTINES

WHERE SPECIFIC_NAME LIKE ''usp_%''

', 'Northwind'

>>>>

When  I run it I get this:

Server: Msg...

Thanks to everyone for their great replies.  I have found two other tools (around $125 each) that work with MSSQL and Oracle. Their output is either XML or HTML or...

Greg,

Can you clarify which stored procs in MSDB are DTS related?

TIA,

Bill

That does help. I'll be toying with the script tomorrow.

Thanks,

Bill

Mharr,

>>  and why not use SQLDMO and the BulkObject to import the data. 

I have been studying SQl-DMO all day. (So I'm a real expert, ha, ha, ha)  But I...

First, I was overwhelemned by the number of interesting responses to this proglem. It will take me some time to absorb it all.

Regarding, SQL-DMO: I don't think our programmers are familiar...

David,

Just saw the quote under "dbo" -- not sysadmin. My fault.

Bill

David,

Excellent quote but where did you find it? I couldn't find it under the updated SQL BOL (typed "systadmin" in the Index.)

TIA,

Bill

Thanks for the thought! I wasn't thinking along that line at all as the developers are not famliar with DTS coding practices and wanted to use the Visual tools.

Bill

Josh,

Sorry I was unclear. What you describe is exactly the case. The database owner is "sa" but each developer belongs to the "db_owner" role for their database. Still I find...

>>

When they create their sp's they just need to write

CREATE PROCEDURE [dbo].[NameOfProcedure]

>>

I agree but they often forget to do this!

Bill

If I recall correctly I did have some good luick with Sql 2K's "Copy Database Wizard"  (under EM) which does the attach\detach for you.

Bill

Yelena,

Be wary of changing drives paths, especially if system databases are involved!

Copying user databases to a new path on a new server is easy (assuming that no tables in the...

"You will want to use backup/restore for the system databases."

Is it that easy? In the past I had to set sql server to single-user mode to restore "master. Not hard...