Presents your 
SQL SERVER E-NEWSLETTER for July 11, 2002

<------------------------------------------->

DROP Non-dbo tables with ease

There are many ways to keep administration simple. One method is to have
all objects within a database owned by dbo. Creating a script that
changes non-dbo database objects to dbo is straightforward.

However, if there are two database objects with the same name and two
owners, the script won't work. The script won't take care of duplicate
object names with different owners. However, the script below will by 
doing the following:


* Create a database user that has permissions to create database objects

* Impersonate the user while creating a table

* Insert commands into a table to change the database object's owner to dbo

* Loop through the table and execute the commands

* Perform the cleanup task, which drops objects, the user, and the login


--***** CREATE A DATABASE USER THAT WILL BEE THE OWNER
EXEC sp_addlogin 'MyUser',null
GO
EXEC sp_adduser MyUser
GO
EXEC sp_addrolemember db_ddladmin, MyUser
GO
--***** IMPERSONATE THE USER WHEN CREATING  OBJECTS
SETUSER 'MyUser'
GO
CREATE TABLE x_temp
(pkey INT IDENTITY,
col1 INT NULL)
GO
--***** RESET THE IMPERSONATION
SETUSER
GO
--***** CREATE COMMANDS TO CHANGE OBJECT OWWNER IN TEMP TABLE
SELECT 'EXEC sp_changeobjectowner ' + '''' +
SUBSTRING(USER_NAME(uid),1,LEN(USER_NAME(uid))) + '.' + name + '''' + 
',' + '''' + 'dbo' + ''''
command
INTO #cmd
FROM sysobjects
WHERE USER_NAME(uid) NOT IN ('INFORMATION_SCHEMA', 'dbo' ,
'system_function_schema' , 'SQLSecure')
     AND type = 'U'
GO
ALTER TABLE #cmd ADD cmd_id INT IDENTITY
GO
--***** LOOP THRU COMMANDS TO CHANGE OBJECTT OWNER
DECLARE @ctr INT, @cmd VARCHAR(255)
SET @ctr = 1
WHILE @ctr < (SELECT COUNT(*) + 1 FROM #cmd)
BEGIN
       SELECT @cmd = command FROM #cmd WHERE cmd_id = @ctr
       SELECT @cmd
       EXEC (@cmd)
       SET @ctr = @ctr + 1
END
GO
--*****  CLEAN UP
DROP TABLE x_temp, #cmd
GO
EXEC sp_droprolemember db_ddladmin, MyUser
GO
EXEC sp_dropuser MyUser
GO
EXEC sp_droplogin MyUser
GO

----------------------------------------

CORRECTION

In a recent tip, "Capture important database information" (June 27,
2002), brackets necessary to the code were mistakenly not included. 
The code in its entirety appears below.

We apologize for any inconvenience this may have caused.

USE master
GO
DECLARE Csr CURSOR FOR
SELECT name
FROM    sysdatabases
ORDER BY NAME

DECLARE
    @name VARCHAR(32), @cmd VARCHAR(255)

SET NOCOUNT ON
OPEN Csr
FETCH NEXT FROM Csr INTO @name
WHILE (@@fetch_status <> -1)
BEGIN

    IF (@@fetch_status <> -2)

    BEGIN
     SELECT @cmd = 'USE [' + @name  + ']
     PRINT REPLICATE(''____' +DB_NAME() + '__'',4) EXEC sp_helpfile'
     EXEC (@cmd)
    END
    FETCH NEXT FROM Csr INTO @name

END
CLOSE Csr
DEALLOCATE Csr

----------------------------------------