Saturday, October 31, 2009

SQL Server: Map Orphaned Users

I've put together the following stored procedure that will map ''orphaned" users in a database.  This often happens when you restore a database into a new environment.

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_map_orphaned_db_users]    Script Date: 10/31/2009 19:44:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/**********************************************************************
 * PROCEDURE NAME:  sp_map_orphaned_db_users
 * DESCRIPTION:
 * Maps orphaned database users to server logins of the same name.
 *
 */
ALTER PROCEDURE [dbo].[sp_map_orphaned_db_users]
(
 @with_report BIT = NULL
,@database NVARCHAR(128) = NULL
,@report_only BIT = NULL /* Pass NULL or 0(zero) to execute and 1 to show actions that would be taken. */
)
AS
BEGIN

    SET NOCOUNT ON

    SELECT
        @report_only = ISNULL(@report_only, 0)
       ,@database = ISNULL(RTRIM(LTRIM(@database)), '')
       ,@with_report = ISNULL(@with_report, 0)

    DECLARE
        @UserName SYSNAME
       ,@dbname SYSNAME
       ,@sql NVARCHAR(4000)
       ,@orphanTableName NVARCHAR(128)

    SELECT
        @orphanTableName = '##orphans_' + REPLACE(CAST(NEWID() AS NVARCHAR(128)), '-', '')

    CREATE TABLE #orphans
    (
     UserName SYSNAME
    ,UserSID VARBINARY(85)
    ,dbname SYSNAME
    )

    SELECT
        @sql = N'
        IF OBJECT_ID(''' + @orphanTableName + ''') IS NOT NULL
        BEGIN
            DROP TABLE ' + @orphanTableName + '
        END
        CREATE TABLE ' + @orphanTableName + '
        (
            UserName SYSNAME,
            UserSID VARBINARY(85),
            dbname SYSNAME
        )'
    EXEC (@sql)
   
    IF @database = ''
        BEGIN

            SELECT
                @sql = N'
            USE [?]
            CREATE TABLE #report
            (
                UserName SYSNAME,
                UserSID VARBINARY(85)
            )

            INSERT INTO #report
            EXEC sp_change_users_login @Action=''Report''

            ALTER TABLE #report
            ADD dbname SYSNAME NULL

            UPDATE #report
            SET dbname = ''?''

            INSERT INTO ' + @orphanTableName + '
            SELECT
                UserName,
                UserSID,
                dbname
            FROM #report'

            EXEC sp_msforeachdb @sql
        END
    ELSE
        BEGIN

            SELECT
                @sql = '
        USE [' + @database + ']
        CREATE TABLE #report
        (
            UserName SYSNAME,
            UserSID VARBINARY(85)
        )

        INSERT INTO #report
        EXEC sp_change_users_login @Action=''Report''

        ALTER TABLE #report
        ADD dbname SYSNAME NULL

        UPDATE #report
        SET dbname = ''' + @database + '''

        INSERT INTO ' + @orphanTableName + '
        SELECT
            UserName,
            UserSID,
            dbname
        FROM #report'

            EXEC (@sql)

        END

    SELECT
        @sql = N'
        INSERT INTO #orphans
        SELECT
            UserName,
            UserSID,
            dbname
        FROM ' + @orphanTableName

    EXEC (@sql)

    SELECT
        @sql = N'
        IF OBJECT_ID(''' + @orphanTableName + ''') IS NOT NULL
        BEGIN
            DROP TABLE ' + @orphanTableName + '
        END'
    EXEC (@sql)

    DECLARE @userCount INT

    SELECT
        @userCount = COUNT(*)
    FROM
        #orphans o
        INNER JOIN master.sys.syslogins l ON l.[name] = o.UserName

    DECLARE rc CURSOR LOCAL FAST_FORWARD
        FOR SELECT
                o.UserName
               ,o.dbname
            FROM
                #orphans o
                INNER JOIN master.sys.syslogins l ON l.[name] = o.UserName

    OPEN rc

    FETCH NEXT FROM rc INTO @UserName, @dbname

    WHILE @@FETCH_STATUS = 0
        BEGIN

            SELECT
                @sql = 'USE ' + @dbname + ' EXEC sp_change_users_login @Action=''update_one'', @UserNamePattern=''' + @UserName + ''', @LoginName=''' + @UserName + ''''
            IF @report_only = 1
                BEGIN
                    PRINT 'WOULD HAVE ASSOCIATED USER [' + @UserName + '] IN DATABASE [' + @dbname + '] WITH SERVER LOGIN [' + @UserName + ']'
                END
            ELSE
                BEGIN
                    EXEC (@sql)
                END

            IF @with_report = 1
                AND @report_only = 0
                BEGIN
                    PRINT 'ASSOCIATED USER [' + @UserName + '] IN DATABASE [' + @dbname + '] WITH SERVER LOGIN [' + @UserName + ']'
                END


            FETCH NEXT FROM rc INTO @UserName, @dbname

        END

    IF (@userCount > 0)
        BEGIN
            PRINT CAST(@userCount AS NVARCHAR(50)) + N' USERS PROCESSED.'
        END
    ELSE
        BEGIN
            PRINT 'NO ORPHANED USERS TO PROCESS.'
        END


    CLOSE rc
    DEALLOCATE rc
END

Friday, October 30, 2009

How to move an object from one schema to another

There are various reasons you may wish to move an object to a different schema.  The reason may be for security purposes or simply for logical grouping.  No matter the reason, the syntax is rather simple:
ALTER SCHEMA newschema TRANSFER  oldschema.objectname
Took me a bit to find this piece of information, so I found it a worthy post!

Thursday, October 29, 2009

Stored Procedure: Dynamic IN Clause Solution

I'm sure many database programmers have run across the issue of wanting to pass a delimited string of values as a parameter, then using that in your IN clause.  Example:

CREATE PROCEDURE MyProc @StringOfValues VARCHAR(200)
AS

SELECT *
FROM table
WHERE column IN (@StringOfValues)

To execute, you do the following:
EXEC MyProc '1,2,3'

If the column data type is an integer, you will likely get the error "Conversion failed when converting the varchar value '1,2,3' to data type int."  Now, you could certainly create a string for the SQL statement - which is a pain to say the least - then EXEC(thestring), but let's look at a nice alternative to that.

First, you will need a table valued function that allows you to pass in a delimited list of values:
CREATE FUNCTION [dbo].[ParamParser]
    (
     @delimString VARCHAR(255)
    ,@delim CHAR(1)
    )
RETURNS @paramtable TABLE ( id INT )
AS BEGIN
    DECLARE @len INT
       ,@index INT
       ,@nextindex INT

    SET @len = DATALENGTH(@delimString)
    SET @index = 0
    SET @nextindex = 0

    WHILE ( @len >= @index )
        BEGIN

            SET @nextindex = CHARINDEX(@delim, @delimString, @index)

            IF ( @nextindex = 0 )
                SET @nextindex = @len + 2

            INSERT  @paramtable
                    SELECT  SUBSTRING(@delimString, @index, @nextindex - @index)

            SET @index = @nextindex + 1

        END
    RETURN
   END

Now that you have that table valued function, you can rewrite your stored procedure as such:
CREATE PROCEDURE MyProc @StringOfValues VARCHAR(200)
AS

SELECT *
FROM table
INNER JOIN dbo.ParamParser(@StringOfValues,',') tvf ON table.column = tvf.id

When you then execute your stored procedure call, you get back the results as expected - filtered accordingly on your delimited string of values.

Tuesday, October 27, 2009

SQL 2008 Replication - Convert from TEXT to VARCHAR(MAX)

SQL Server Replication has evidently come a long way since I began using it. Back in the day it seemed like every action was equivalent to a visit to the dentist, but today after hours of searching and digging, I found the solution to my problem was actually quite simple.

The task at hand was that that I had three columns that I wanted to convert from TEXT data types to VARCHAR(MAX).

First I tried the obvious: simply changing the data type via the SSMS GUI, but received the following error "Unable to modify table. Cannot drop the table 'tablename' because it is being used for replication." Strike one.

Next, I tried to script out my changes, but SSMS gave me a pretty awful script that basically created a temporary table, migrated the data, dropped the original, and renamed the temporary table back. That just didn't sound like a sound plan in a replicated environment.

Then I started investigating the replication stored procedures to see if there was one that allowed me to change the properties of an article (table in this case), to no avail.

Continuing on, I considered removing the article from the publication, modifying the column, then adding it back into the publication. Now, while I figured this method would work, I wanted to try one last thing: I did recall reading that running the raw ALTER TABLE ALTER COLUMN command may work, and having not tried that, I gave it a shot.

I opened a new query window and ran the following:
ALTER TABLE
tablename ALTER COLUMN columnname VARCHAR(MAX)

BINGO! I checked my subscribers and within seconds they too inherited the schema change (which BTW is an option you can select on the publication properties).

Too easy... but had to share. Hopefully it sames someone some time down the road.