Reputation: 1715

SQL - Dropping a user on all databases on an instance without a cursor or sp_foreach

Goal:

Iterate through every database on a server and delete a user, while listing the database name and also an error message if there is an error, for example if the user does not exist on that db.

The Problem:

The code runs but I don't think it's actually checking every server. I say that because I know the user exists on one particular database, but the message I get is 'User 'username' does not exist.

The column DBName is showing all of the DB's on the server, but why would this code show the user does-not-exist on the DB where I know they exist?

Thanks for your help!

    USE master;
    SET NOCOUNT ON;
    IF OBJECT_ID ('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;

    SELECT  name
          , 0 AS completed
    INTO    #tmp
    FROM    sys.databases
    WHERE   name NOT IN ( 'tempdb', 'Training2012' );   -- A list of DBs you don't wish to include

    DECLARE @dbname sysname;

    DECLARE @cmd NVARCHAR(4000);
    DECLARE @cmd2 NVARCHAR(4000) = N'
    DECLARE  @user1 VARCHAR(15) = ''username''
    BEGIN TRY
        IF (EXISTS (SELECT  * FROM  sys.database_principals WHERE   name = @user1)) DROP USER [@user1];
    ELSE SELECT  @dbname AS DBName, ''User '' + @user1 +'' does not exist'' AS DOES_NOT_EXIST;
    END TRY
    BEGIN CATCH
    SELECT  @dbname            AS DBName
          , @user1                AS [User]
          , ERROR_MESSAGE ()      AS ErrorMsg          
    END CATCH;'


    WHILE EXISTS (SELECT 1 FROM #tmp WHERE  completed = 0)
    BEGIN

        SET @dbname = (SELECT TOP 1 name FROM #tmp WHERE  completed = 0 ORDER BY  name); -- You can ORDER BY name if you care about the order       

        BEGIN TRY
            --SET @cmd
            --EXEC sp_executesql @cmd2;
             EXEC sp_executesql @cmd2, N'@dbname sysname',@dbname = @dbname;
            UPDATE  #tmp
            SET     completed = 1
            WHERE   name = @dbname;
        END TRY
        BEGIN CATCH
            SELECT  @@SERVERNAME     AS ServerName
                  , DB_NAME ()       AS DBName
                  , ERROR_MESSAGE () AS ErrorMessage
                  , ERROR_LINE ()    AS ErrorLine;
        END CATCH;
    END;

Upvotes: 0

Answers (2)

Thom A

Reputation: 95620

I did write something a little while ago that might work for this, which doesn't use a CURSOR to run commands through databases, that I called sp_foreachdatabase: A CURSOR free version of sp_msforeachdb. The definitions are below:

USE master;
GO
IF NOT EXISTS (SELECT 1 FROM sys.types WHERE [name] = N'objectlist')
    CREATE TYPE dbo.objectlist AS table ([name] sysname);

GO

USE master;
GO
CREATE OR ALTER PROC sp_foreachdatabase @Command nvarchar(MAX),
                                        @Delimit_Character nchar(1) = N'?', --Character to be replaced with a delimit identified version of the datbaase name I.e. [master]
                                        @Quote_Character nchar(1) = N'&', --Character to be replaced with a single quoted (') version of the datbaase name I.e. 'master'
                                        @Skip_System bit = 0, --Omits master, msdb, tempdb and model. Ignored if @Database_List has data.
                                        @Skip_User bit = 0, --Omits all user databases. Ignored if @Database_List has data.
                                        @Database_List dbo.objectlist READONLY, --If @Skip_System and @Skip_User equal 1, and this is empty, an error will be thrown
                                        @Auto_Use bit = 0, --Automatically starts each command agaisnt a database with a USE
                                        @Exit_On_Error bit = 1, --If an error is occurs against a single database, the command will still be run against the remainder. Otherwise everything is rolled back
                                                                --This does not effect the @Pre_Command and @Post_Command statements
                                        @Pre_Command nvarchar(MAX) = NULL, --Command to run before @Command. Does not use Character Replacements. Run against master DB.
                                        @Post_Command nvarchar(MAX) = NULL, --Command to run after @Command. Does not use Character Replacements. Run against master DB.
                                        @Command_Run nvarchar(MAX) = NULL OUTPUT  --Returns the generated and replaced command, for trouble shooting
AS BEGIN

    --Do some checking of passed values first
    --Check that @Skip_System, @Skip_User aren't both 0 or that @Database_List has some rows
    IF (@Skip_System = 1 AND @Skip_User = 1 AND NOT EXISTS (SELECT 1 FROM @Database_List))
        THROW 62401, N'System and User databases cannot be skipped if a Database List is not supplied.', 16;

    IF @Delimit_Character IS NULL
        THROW 62402, N'@Delimit_Replace cannot have a value of NULL.', 16;

    IF @Quote_Character IS NULL
        THROW 62403, N'@Quoted_Replace cannot have a value of NULL.', 16; 

    IF @Skip_User IS NULL
        THROW 62404, N'@Skip_User cannot have a value of NULL.', 16;

    IF @Skip_System IS NULL
        THROW 62405, N'@Skip_System cannot have a value of NULL.', 16;

    IF @Auto_Use IS NULL
        PRINT N'@Auto_Use has a value of NULL. Behaviour will be as if the value is 0.';

    DECLARE @CRLF nchar(2) = NCHAR(13) + NCHAR(10);
    DECLARE @RC int;

    --Add the Pre Command to the batch
    SET @Command_Run = ISNULL(N'/* --- Pre Command Begin. --- */' + @CRLF + @CRLF + N'USE master;' + @CRLF + @CRLF + @Pre_Command + @CRLF + @CRLF + N'/* --- Pre Command End. --- */', N'');

    --Get the databases we need to deal with
    --As @Database_List might be empty and it's READONLY, and we're going to do the command in database_id order we need another variable.
    DECLARE @DBs table (database_id int,
                        database_name sysname);
    IF EXISTS (SELECT 1 FROM @Database_List)
        INSERT INTO @DBs (database_id,database_name)
        SELECT d.database_id,
               d.[name]
        FROM sys.databases d
             JOIN @Database_List DL ON d.[name] = DL.[name];
    ELSE
        INSERT INTO @DBs (database_id,database_name)
        SELECT d.database_id,
               d.[name]
        FROM sys.databases d
        WHERE (d.database_id <= 4 AND @Skip_System = 0) OR (d.database_id > 4 AND @Skip_User = 0);

    SET @Command_Run = @Command_Run + @CRLF + @CRLF +
                       N'/* --- Begin command for each database. --- */' + @CRLF + @CRLF +
                       CASE WHEN @Exit_On_Error = 0 THEN N'--Turning XACT_ABORT off due to @Exit_On_Error parameter' + @CRLF + @CRLF + N'SET XACT_ABORT OFF;' + @CRLF + N'DECLARE @Error nvarchar(4000);' ELSE N'SET XACT_ABORT ON;' END +
                       (SELECT @CRLF + @CRLF + 
                               N'/* --- Running @Command against database ' + QUOTENAME(DB.database_name,'''') + N'. --- */' + @CRLF + @CRLF +
                               CASE WHEN @Auto_Use = 1 THEN N'USE ' + QUOTENAME(DB.database_name) + N';' + @CRLF + @CRLF ELSE N'' END +
                               N'BEGIN TRY' + @CRLF + @CRLF +
                               REPLACE(REPLACE(@Command, @Delimit_Character, QUOTENAME(DB.database_name)),@Quote_Character, 'N' + QUOTENAME(DB.database_name,'''')) + @CRLF + @CRLF +
                               'END TRY' + @CRLF +
                               N'BEGIN CATCH' + @CRLF +
                               CASE WHEN @Exit_On_Error = 0 THEN N'    SET @Error = N''The following error occured during the batch, but has been skipped:'' + NCHAR(13) + NCHAR(10) + ' + @CRLF +
                                                                 N'                 N''Msg '' + CONVERT(nvarchar(6),ERROR_NUMBER()) + '', Level '' + CONVERT(nvarchar(6),ERROR_SEVERITY()) + '', State '' + CONVERT(nvarchar(6),ERROR_STATE()) + '', Line '' + CONVERT(nvarchar(6),ERROR_LINE()) + NCHAR(13) + NCHAR(10) +' + @CRLF + 
                                                                 N'                 ERROR_MESSAGE();' + @CRLF +
                                                                 N'    PRINT @Error;' + @CRLF +
                                                                 N'    SET @RC = ERROR_NUMBER();'
                                                            ELSE N'    THROW;'
                               END + @CRLF +
                               N'END CATCH;' + @CRLF +
                               N'/* --- Completed @Command against database ' + QUOTENAME(DB.database_name,'''') + N'. --- */'
                        FROM @DBs DB
                        FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)') + @CRLF + @CRLF +
                        CASE WHEN @Exit_On_Error = 0 THEN N'--Turning XACT_ABORT back on due to @Exit_On_Error parameter' + @CRLF + @CRLF + N'SET XACT_ABORT ON;' ELSE N'' END;

    SET @Command_Run = @Command_Run + ISNULL(@CRLF + @CRLF + N'/* --- Post Command Begin. --- */' + @CRLF + @CRLF + N'USE master;' + @CRLF + @CRLF + @Post_Command + @CRLF + @CRLF + N'/* --- Post Command End. --- */', N'');

    EXEC sp_executesql @Command_Run, N'@RC int OUTPUT', @RC = @RC;

    SET @RC = ISNULL(@RC, 0);
    RETURN @RC;

END;
GO

Part of why i think this would be relevant is because of the parameter @Exit_On_Error, where you specifically wanted a message if there's a problem. To quote my article:

One advantage that a Cursor would have is that each statement against a database would be run in it’s own batch; meaning that if one failed the others wouldn’t be effected. The @Exit_On_Error parameter adds this functionality by Catching the error and then printing it. The SP also returns the error number of the last error number returned. Note that a TRY...CATCH doesn’t work for all types of errors (such as an invalid object name).

Dropping a USER that doesn't exist would error, but not cause a batch failure with this parameter set to 0. Therefore you could do something like the below:

USE master;
GO

DECLARE @User sysname = N'YourUser'; --This is your user parameter

DECLARE @SQL nvarchar(MAX) = N'DROP USER ' + QUOTENAME(@User) + N';' --The SQL to run in each database

DECLARE @Command_Run nvarchar(MAX); --This is an OUTPUT parameter
DECLARE @Database_List dbo.objectlist; --Needs to be declared, as required, but nothing will be inserted
EXEC dbo.sp_foreachdatabase @Command = @SQL,
                            @Skip_System = 1, --Assumed skipping system databases
                            @Skip_User = 0, --Assumed we want user databases
                            @Database_List = @Database_List, --This is empty
                            @Auto_Use = 1, --Puts a USE statement at start of each database query
                            @Exit_On_Error = 0, --Causes a PRINT of errors, but doesn't THROW them
                            @Command_Run = @Command_Run OUTPUT; --The SQL that's run, in case you need it.

On my instance, this doesn't DROP any users, so outputs the below:

The following error occured during the batch, but has been skipped:
Msg 15151, Level 1, State 1, Line 16
Cannot drop the user 'testUser', because it does not exist or you do not have permission.
The following error occured during the batch, but has been skipped:
Msg 15151, Level 1, State 1, Line 34
Cannot drop the user 'testUser', because it does not exist or you do not have permission.
The following error occured during the batch, but has been skipped:
Msg 15151, Level 1, State 1, Line 52
Cannot drop the user 'testUser', because it does not exist or you do not have permission.
The following error occured during the batch, but has been skipped:
Msg 15151, Level 1, State 1, Line 70
Cannot drop the user 'testUser', because it does not exist or you do not have permission.

You can also see the SQL run, from @Command_Run, which shows the below:

/* --- Begin command for each database. --- */

--Turning XACT_ABORT off due to @Exit_On_Error parameter

SET XACT_ABORT OFF;
DECLARE @Error nvarchar(4000);

/* --- Running @Command against database 'Sandbox'. --- */

USE [Sandbox];

BEGIN TRY

DROP USER testUser

END TRY
BEGIN CATCH
    SET @Error = N'The following error occured during the batch, but has been skipped:' + NCHAR(13) + NCHAR(10) + 
                 N'Msg ' + CONVERT(nvarchar(6),ERROR_NUMBER()) + ', Level ' + CONVERT(nvarchar(6),ERROR_STATE()) + ', State ' + CONVERT(nvarchar(6),ERROR_STATE()) + ', Line ' + CONVERT(nvarchar(6),ERROR_LINE()) + NCHAR(13) + NCHAR(10) +
                 ERROR_MESSAGE();
    PRINT @Error;
    SET @RC = ERROR_NUMBER();
END CATCH;
/* --- Completed @Command against database 'Sandbox'. --- */

/* --- Running @Command against database 'CaseSensitive'. --- */

USE [CaseSensitive];

BEGIN TRY

DROP USER testUser

END TRY
BEGIN CATCH
    SET @Error = N'The following error occured during the batch, but has been skipped:' + NCHAR(13) + NCHAR(10) + 
                 N'Msg ' + CONVERT(nvarchar(6),ERROR_NUMBER()) + ', Level ' + CONVERT(nvarchar(6),ERROR_STATE()) + ', State ' + CONVERT(nvarchar(6),ERROR_STATE()) + ', Line ' + CONVERT(nvarchar(6),ERROR_LINE()) + NCHAR(13) + NCHAR(10) +
                 ERROR_MESSAGE();
    PRINT @Error;
    SET @RC = ERROR_NUMBER();
END CATCH;
/* --- Completed @Command against database 'CaseSensitive'. --- */

/* --- Running @Command against database 'AdventureWorks2012'. --- */

USE [AdventureWorks2012];

BEGIN TRY

DROP USER testUser

END TRY
BEGIN CATCH
    SET @Error = N'The following error occured during the batch, but has been skipped:' + NCHAR(13) + NCHAR(10) + 
                 N'Msg ' + CONVERT(nvarchar(6),ERROR_NUMBER()) + ', Level ' + CONVERT(nvarchar(6),ERROR_STATE()) + ', State ' + CONVERT(nvarchar(6),ERROR_STATE()) + ', Line ' + CONVERT(nvarchar(6),ERROR_LINE()) + NCHAR(13) + NCHAR(10) +
                 ERROR_MESSAGE();
    PRINT @Error;
    SET @RC = ERROR_NUMBER();
END CATCH;
/* --- Completed @Command against database 'AdventureWorks2012'. --- */

/* --- Running @Command against database 'TestBed'. --- */

USE [TestBed];

BEGIN TRY

DROP USER testUser

END TRY
BEGIN CATCH
    SET @Error = N'The following error occured during the batch, but has been skipped:' + NCHAR(13) + NCHAR(10) + 
                 N'Msg ' + CONVERT(nvarchar(6),ERROR_NUMBER()) + ', Level ' + CONVERT(nvarchar(6),ERROR_STATE()) + ', State ' + CONVERT(nvarchar(6),ERROR_STATE()) + ', Line ' + CONVERT(nvarchar(6),ERROR_LINE()) + NCHAR(13) + NCHAR(10) +
                 ERROR_MESSAGE();
    PRINT @Error;
    SET @RC = ERROR_NUMBER();
END CATCH;
/* --- Completed @Command against database 'TestBed'. --- */

--Turning XACT_ABORT back on due to @Exit_On_Error parameter

SET XACT_ABORT ON;

Upvotes: 2

D Kramer

Reputation: 759

Think you're at the moment not finding any users.

You're getting information from sys.database_principals, but you're running it from the master. the sys.database_principals is different per database. Don't think your dynamic sql is actually valid either.

I.e. if you have databases

Test
ABC
Production

You'd need to check for user existance on each of these database by either running in their context:

USE Test
GO

SELECT * FROM  sys.database_principals WHERE name = @userName

Or query it from a different context/db by appending the database infront of the schema.

SELECT * FROM  Test.sys.database_principals WHERE name = @userName
SELECT * FROM  ABC.sys.database_principals WHERE name = @userName
SELECT * FROM  Production.sys.database_principals WHERE name = @userName

As it stands you're just checking the master database.

Upvotes: 0

SQL - Dropping a user on all databases on an instance without a cursor or sp_foreach

Goal:

The Problem:

Answers (2)

Related Questions