Bulk update on scripts (like triggers)

[teqmem]

Hello,

How can I do a bulk update on the scripts of all my triggers, procedures, user define functions and views so I can change some string to another.

For example, we had hardcoded the owner of the tables that the above objects refer to, but now need to change to something else. So now I may have something like “SELECT * FROM someuser.sometable” to “SELECT * FROM [somedomain\domainuser].sometable”.

Thank you.

 

[mrdenny]

And this would be why it's best to not set ownership to a user.

Script out all the objects to a single file and do a find and repalce in your favorite text editor (such as notepad) and then run the script.

Don't forget to backup the database first incase something goes very, very wrong.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[teqmem]

How would you do it where statements don't need the username? We're directed that all SQL Server objects be owned by a Windows User since the hosting SQL Server 2000 Servers only allows Windows Authentication.

Currently, having to hardcode the machine name and user name in the transact-sql scripts create a big headache as we will be loading our application on multiple domains and servers.

Suggestions?

Thanks.

 

[bborissov]

SQL Server 2000 Servers only allows Windows Authentication.

Not true!
SQL Server 2000 and above accept not ONLY Windows Authentication but Mixed one also.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[teqmem]

The problem is that we have no control of the SQL Server 2000 environment. Our application is only a "guest" application. We were told that the environment only runs in "Windows Authentication" only and we have to deal with it.

So how can get around the problem of the hardcoded user name?

Thanks.

 

[mrdenny]

Have the application run under the Windows Login which owns the objects and call the objects using just the object name.

Instead of calling the object [Domain\user].TableName just called it via TableName. This way you don't care who the table objects are owned by.

That or create all your objects under the owner dbo and call all the objects as having the owner dbo as dbo exists in all databases.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[teqmem]

Is it a true statement that with SQL Server 2000, you must prefix user-defined functions by the owner of that function?

With my test, I used 'SQL Query Analyzer' and I logged in as the owner (Windows Authentication) but I can only successfully invoke a function when I prefix the function with the owner such as:

select SOME_FUNCTION;

generates

Server: Msg 195, Level 15, State 10, Line 3
'SOME_FUNCTION' is not a recognized function name.

but once prefixed, it's okay.

select [some_domain\some_user].SOME_FUNCTION;

 

[mrdenny]

Is it a true statement that with SQL Server 2000, you must prefix user-defined functions by the owner of that function?

Yes this is true. It holds true in SQL 2005 and the current build of SQL 2008 (July 2008).

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog