Fixing orphaned SQL users

2 Awesome Blog Posts By Doug Nelson

I came across two awesome blog posts by Doug Nelson.

Optional Parameters for Stored Procedures
GridView Dissapointments

Useful SQL Script

Below is a script that I use to change the owners of all objects on a database to dbo.

------------------------------------------------------------------
-- Name: dbo.sp_FixObjOwners
-- Auth: Sal Terillo ( )
-- Date: 10/1/2002
-- finds objects that are not owned by dbo
-- and changes them to dbo
-- this eliminates the problem with objects accidentally
-- created as being owned by someone other than dbo
-- REVS:
--
------------------------------------------------------------------

CREATE PROC dbo.up_FixObjOwner
AS
   SET NOCOUNT ON

   DECLARE @dynsql VARCHAR ( 1000 )
   SET @dynsql = ''

   DECLARE @Obj_Owner sysname
   SET @Obj_Owner = ''

   DECLARE @Obj_Type VARCHAR ( 30 )
   SET @Obj_Type = ''

   DECLARE @Obj_Name sysname
   SET @Obj_Name = ''

   DECLARE @ObjCounter INT
   SET @ObjCounter = 0

   DECLARE @DBO CHAR ( 3 )
   SET @DBO = 'DBO'

   -- temp table to hold all objects not owned
   -- by DBO
   CREATE TABLE  #ChangeOwners (
       id INT identity ( 1 , 1 ),
      Obj_Owner sysname ,
      Obj_Name sysname ,
      Obj_Type VARCHAR ( 30 ))

   -- populate it
   INSERT #ChangeOwners ( Obj_Owner , Obj_Name , Obj_Type )
   SELECT
      su. name ,
      so. name ,
       CASE
          WHEN type = 'u'   THEN 'table'
          WHEN type = 'p'   THEN 'sproc'
          WHEN type = 'v'   THEN 'view'
       END AS  obj_type
   FROM sysusers su
   JOIN sysobjects so
   ON su.uid = so.uid
   WHERE su. name   NOT IN   ( 'information_schema' , 'dbo' )
   AND so.type  IN ( 'p' , 'u' , 'v' )

   -- select * from #ChangeOwners
    SET @ObjCounter = @@rowcount     -- holds the count of rows inserted into #ChangeOwners

   WHILE @Objcounter > 0
       BEGIN
          -- construct string for object ownership change
          SELECT @Obj_Name = Obj_Owner + '.' + Obj_Name FROM #ChangeOwners WHERE id =
@ObjCounter
          SELECT @Obj_Type = Obj_Type FROM #ChangeOwners WHERE id = @ObjCounter

          SET @dynsql = 'sp_ChangeObjectOwner ''' + @Obj_Name + ''', ' + @DBO
          --select @dynsql
          PRINT 'changing ownership on ' + @Obj_Type + ': ' + @Obj_Name
          EXEC ( @dynsql )
          SET @ObjCounter = @ObjCounter - 1
       END

   -- ok all done, collect garbage
   DROP TABLE  #ChangeOwners
GO

Cool Embed SQL DB

VistaDB 2.1 database for .NET has been released

This 2.1 update includes over 60 improvements, including new support for .NET 2.0 and Visual Studio 2005. VistaDB is a small-footprint, embedded SQL database alternative to Jet/Access, MSDE and SQL Server Express 2005 that enables developers to build .NET 1.1 and .NET 2.0 applications. Features SQL-92 support, small 500KB embedded footprint, free 2-User VistaDB Server for remote TCP/IP data access, royalty free distribution for both embedded and server, Copy 'n Go! deployment, managed ADO.NET Provider, data management and data migration tools.

Free trial is available for download.
- Learn more about VistaDB
- Repost this to your blog and receive a FREE copy of VistaDB 2.1!