Change schema name on Tables and Stored procedures in SQL Server 2005

I was trying to change the ‘schema’ name on an object from ‘username’ to ‘dbo’ in SQL Server 2005. 
The script generates code you have to copy, paste and run on the database. 
Thanks to the DBA gurus on the sql-sqlsvr-sprocs@sqladvice.com list that provided the syntax. 
I encourage everyone looking for SQL help to check out the lists on
SQLAdvice.com http://sqladvice.com/lists/category.aspx?c=32 


Change Stored procedures in sql 2005


‘Note this is the raw query
SELECT
‘ALTER SCHEMA dbo TRANSFER ‘ + s.Name + ‘.’ + p.Name FROM sys.Procedures p INNER JOIN
sys.Schemas s on p.schema_id = s.schema_id WHERE s.Name = ‘CHANGE_ME_Username’


   It would create this kind of output.



  • ALTER SCHEMA dbo TRANSFER steveschofield.spAuthors1
  • ALTER SCHEMA dbo TRANSFER steveschofield.spAuthors2
  • ALTER SCHEMA dbo TRANSFER steveschofield.spAuthors3

You would run in a new query window, after this refresh SQL Management studio
and the stored procedures would be dbo.spAuthors1, dbo.spAuthors2, dbo.spAuthors3.


Change Table in sql 2005


Here is an article that describes the syntax, it uses the ‘sp_changeobjectowner’
which isn’t real “sql2005’ish” but works. 


declare @OldOwner varchar(100) declare @NewOwner varchar(100) 
set @OldOwner = ‘OldOwner’ set @NewOwner = ‘NewOwner’

select ‘sp_changeobjectowner ”[‘ + table_schema + ‘].[‘ +
table_name + ‘]”, ”’ + @NewOwner + ”’ go
from information_schema.tables where Table_schema = @OldOwner

Create Export & Encrypt connection string on server and dev machine with ASP.Net 2.0

This article covers some basic steps and commands to encrypt your connection string and other items in a configuration file using ASP.NET 2.0.  Microsoft has made it much easier to have a portable key that encrypts certain sections in a web.config that are normally clear text.  I haven’t found a quick how-to reference to allow for a scenerio where the key is both on a local development machine along with being on a remote web server.  Developers like to test out their code locally before publishing to production.  In our case we have shared clients at ORCS Web that want to encrypt their information for added security.  ASP.NET 2.0 makes this really simple.  I’m not going to cover this topic and I’m assuming you already know this.  If not there are several good articles that explain the architecture, basic commands etc.  This article covers the scenerio of creating a key on a production server then exporting the keys to an XML file where the developer can import and use on their local machine. The web.config will be encrypted both on their local dev box as well as the remote server using the same RSA key.

Links to articles covering Encrypting connection strings

This has to be run on the server

How to create a key locally on shared server.  The example is called ‘YourCustomKey’ but this can be anything.  These are stored in C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys Make sure the everyone group has proper folder permissions to make this work

 

‘step 1 – Add to container
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis -pc “YourCustomKey” -exp

‘Step 2 – Added to web.config at the root of the folder for the website.  This has to be there prior to encrypting.  This would be placed in the
<configuration>
   <configProtectedData>
     <providers>
       <add keyContainerName=”YourCustomKey” useMachineContainer=”true” description=”Uses RsaCryptoServiceProvider to encrypt and decrypt” name=”YourCustomKey” type=”System.Configuration.RsaProtectedConfigurationProvider,System.Configuration, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a” />
     </providers>
   </configProtectedData>

……….the rest of your web.config settings.

</configuration>

‘Step 3 — Encrypt (can also put quotes around it). 
Note: the c:\inetpub\wwwroot would be replaced with the absolute file path on your system and the web.config settings you are doing, this example assumes the connectionStrings part

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis -pef connectionStrings c:\inetpub\wwwroot -prov YourCustomKey (web.config is assumed to already be present)

‘Decrypt —
Note: the c:\inetpub\wwwroot would be replaced with the absolute file path on your system and the web.config settings you are doing, this example assumes the connectionStrings part

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis -pdf connectionStrings c:\inetpub\wwwroot -prov YourCustomKey

 

 

To export and give to client to run on their own machine in an XML file.

1.                 aspnet_regiis -px “YourCustomKey” “C:\temp\CustomKeys.xml” -pri

 

The -pri switch causes the private and public key to be exported. This enables both encryption and decryption. Without the–pri switch, you would only be able to encrypt data with the exported key.

 

 

Client has to run this on their local machine

 

Here’s my command line session:

C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0>aspnet_regiis.exe -pz “YourCustomKey”
Deleting RSA Key container…
Succeeded!

C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0>aspnet_regiis.exe -pi “YourCustomKey” “c:\temp\CustomKeys.xml” -exp
Importing RSA Keys from file..
Succeeded!

(At this point the web app with the encrypted web.config works locally)

C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0>aspnet_regiis.exe -pdf connectionStrings “C:\Documents and Settings\Steve Schofield\My Documents\Visual Studio 2005\Web Sites\YourWebsitePath”
Decrypting configuration section…
Succeeded!

(At this point I check web.config and see its decrypted)

C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0>aspnet_regiis.exe -pef connectionStrings “C:\Documents and Settings\Steve Schofield\My Documents\Visual Studio 2005\Web Sites\YourWebsitePath” -prov
“YourCustomKey”
Encrypting configuration section…
Succeeded!

Check web.config and its encrypted)

Last Test

Upload web.config to remote host

Steve Schofield
Microsoft MVP – ASP/ASP.NET
ASPInsider Member – MCP
 
http://www.orcsweb.com/
Managed Complex Hosting
#1 in Service and Support