Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Script to delete orphaned users


Script to delete orphaned users

Author
Message
river1
river1
SSC Eights!
SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)

Group: General Forum Members
Points: 950 Visits: 1350
Hi,

I have deleted some logins from my SQL Server 2005 servers, now i need a script to delete the users of this logins from my user databases does sameone have a script like this?


Thank you
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2924 Visits: 4076
its working in my side , please test before run it ; "PRINT @cmd"
for it
USE [master]
GO
   SET NOCOUNT ON
   DECLARE @cmd varchar(4000)

   BEGIN TRY
      Create table #Orphan_User_Tbl
      (
         [Database_Name] sysname ,
         [Orphaned_User] sysname
      Wink
      SET NOCOUNT ON   

      Create table #Windows_Auth_Orphan_User
      (
      [Str] nvarchar(300)
      Wink
      DECLARE @DBCount INT,@MaxCount INT, @Qry nvarchar(4000), @DBName sysname
      DECLARE @db_list table (dbname nvarchar(100),ID int identity)
      SET @Qry = ''
      SET @DBCount = 1
            INSERT INTO @db_list(dbname )
               SELECT name FROM sys.sysdatabases
               WHERE dbid > 4
         
         SELECT @MaxCount = MAX(ID) FROM @db_list
         WHILE(@DBCount < = @MaxCount )
            BEGIN
               SELECT @DBName = dbname FROM @db_list WHERE id = @DBCount
               SET @Qry = '   SELECT ''' + @DBName + ''' AS [Database Name],
                     CAST(su.name AS sysname) AS [Orphaned User]
                     FROM ' + QUOTENAME(@DBName) + '..sysusers su
                     inner join master..syslogins b
                        on su.name=b.name
                     where
                        su.sid is not null
                        and su.sid not in (0x00,0x01)
                     and su.sid <> b.sid'
               INSERT INTO #Orphan_User_Tbl EXEC (@Qry)
               SET @DBCount = @DBCount + 1
            END

         DECLARE MC CURSOR
         READ_ONLY
         FOR
            
         SELECT [Database_Name]+ '..sp_change_users_login ''UPDATE_ONE'' , ''' + Orphaned_User + ''' ,''' + Orphaned_User + ''';'
         FROM #Orphan_User_Tbl
         ORDER BY [Database_Name], [Orphaned_User]
            
         OPEN MC
         FETCH NEXT FROM MC INTO @cmd
            WHILE (@@fetch_status <> -1)
               BEGIN
                  IF (@@fetch_status <> -2)
                      BEGIN TRY
                        PRINT @cmd
                        Execute (@cmd)
                     END TRY
                     BEGIN CATCH
                        SELECT    ERROR_NUMBER()ErrorNumber,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage
                        FETCH NEXT FROM MC INTO @cmd
                     CONTINUE;
                     END CATCH   
               FETCH NEXT FROM MC INTO @cmd
               END
         
         CLOSE MC
         DEALLOCATE MC
---------------------------------------------------------------------------------------------------------
   ----Fixing Windows autheticated user-----------------
      SET @DBCount = 1       
         WHILE(@DBCount < = @MaxCount )
            BEGIN
               SELECT @DBName = ''+ dbname + '' FROM @db_list WHERE id = @DBCount
               SET @Qry = 'SELECT '' USE [' + @DBName + ' ];
                           ALTER USER ['' + NAME + ''] WITH LOGIN = [ '' + NAME + '']''
                           FROM msdb.sys.database_principals
                           WHERE ( type_desc = ''WINDOWS_GROUP'' OR type_desc = ''WINDOWS_USER'' )
                                 AND name NOT like ''%dbo%'' AND name NOT LIKE ''%#%'''   
            INSERT INTO #Windows_Auth_Orphan_User EXEC (@Qry)
            SET @DBCount = @DBCount + 1
            END      

      --SELECT * FROM #Windows_Auth_Orphan_User
      DECLARE MC CURSOR READ_ONLY FOR
      SELECT [Str]FROM #Windows_Auth_Orphan_User
         OPEN MC
         FETCH NEXT FROM MC INTO @cmd
            WHILE (@@fetch_status <> -1)
               BEGIN
                  IF (@@fetch_status <> -2)
                      BEGIN TRY
                        PRINT @cmd
                        Execute (@cmd)
                     END TRY
                     BEGIN CATCH
                        SELECT    ERROR_NUMBER()ErrorNumber,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage
                        FETCH NEXT FROM MC INTO @cmd
                     CONTINUE;
                     END CATCH   
               FETCH NEXT FROM MC INTO @cmd
               END
         CLOSE MC
         DEALLOCATE MC

      DROP Table #Orphan_User_Tbl
      Drop table #Windows_Auth_Orphan_User

   END TRY

   BEGIN CATCH
      SELECT    ERROR_NUMBER()ErrorNumber,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage
   END CATCH










-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Adi Cohn-120898
Adi Cohn-120898
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2222 Visits: 6076
Here is one way of doing so. The script bellow creates a script that deletes all orphaned users from each database. Notice that the script is using an undocumented procedure (sp_MSforeachdb) that could be modified in the future by Microsoft without any warning, so using it in a user application is something that you shouldn’t do (I only use it in some script that only me or another DBA is using). Also you should check the script that is created for other users that might have been created in the database, and that you don’t want to drop them.

exec sp_MSforeachdb 'use ?; select ''use ? '' + ''
go'' + ''
drop user '' + sdp.name from sys.database_principals sdp
left join sys.server_principals ssp on sdp.sid = ssp.sid
where ssp.sid is null and sdp.type in (''S'',''U'',''G'')
and sdp.name not in (''guest'', ''INFORMATION_SCHEMA'', ''sys'', ''BROKER_USER'', ''dbo'')'



Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
river1
river1
SSC Eights!
SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)

Group: General Forum Members
Points: 950 Visits: 1350
Bhuvnesh your solution is not what i asked for.
Your solution is not to delete User accounts that are orphan because the login was deleted...

But thanks.

I will try the other solution
Richard M.
Richard M.
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1114 Visits: 2668
Here's the solution we use:
-- 1) List all Orphaned users on existing DB
-- 2) Associate the DB User with the server Login, if existing
-- 3) If 2) fails, try to delete associated DB Schema
-- 4) Drop User

DECLARE @UserName varchar(100),
      @SQL as varchar(max)

CREATE TABLE
   #users (
         Username varchar(100),
         UserSID varbinary (85)
         Wink
-- 1)
INSERT INTO
   #users
   exec sp_change_users_login @Action='Report'

DECLARE mycurs CURSOR FOR
   SELECT
      Username
   FROM
      #users
      
OPEN mycurs
FETCH NEXT FROM mycurs
INTO @username

WHILE @@FETCH_STATUS = 0
BEGIN

   -- 2)
   BEGIN TRY
   EXEC sp_change_users_login @Action='update_one', @UserNamePattern=@UserName, @LoginName=@UserName
   END TRY

   BEGIN CATCH
      print @Username + ' does not exist as a Login. Deleting. '
      
      -- 3)
      BEGIN TRY
         SET @SQL = 'DROP SCHEMA ' + @UserName
         EXEC (@SQL)
      END TRY
      BEGIN CATCH
      END CATCH
      
      -- 4)
      SET @SQL = 'DROP USER ' + @UserName
      EXEC (@SQL)
   END CATCH
FETCH NEXT FROM mycurs
INTO @username
END

CLOSE mycurs
DEALLOCATE mycurs

DROP TABLE #users



_______________________________________________________________________
For better assistance in answering your questions, click here
river1
river1
SSC Eights!
SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)

Group: General Forum Members
Points: 950 Visits: 1350
Thank you all for your replys.

I will use this one:


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER Procedure [dbo].[DropOrphanUserAccounts]
as
BEGIN

set nocount on

create table #UserAccountsToDelete
(
ID int identity,
DB varchar(100),
[User] varchar(100)
)





DECLARE @DatabaseName nvarchar(100)
   DECLARE my_DBs CURSOR FAST_FORWARD FOR
   select [name] from master.sys.databases
   WHERE [NAME] NOT IN ('MASTER','MODEL','TEMPDB','MSDB','ADVENTUREWORKS')
   OPEN my_DBs
   FETCH NEXT FROM my_DBs INTO @databasename
   WHILE @@FETCH_STATUS = 0
   Begin


insert #UserAccountsToDelete
exec('select '''+@databasename+''',sdp.name from '+@DatabaseName+'.sys.database_principals sdp
    left join sys.server_principals ssp on sdp.sid = ssp.sid
where ssp.sid is null and sdp.type in (''S'',''U'',''G'')
and sdp.name not in (''guest'', ''INFORMATION_SCHEMA'', ''sys'', ''BROKER_USER'', ''dbo'')')


   FETCH NEXT FROM my_DBs INTO @databasename
   END
   CLOSE my_DBs
   DEALLOCATE my_DBs

declare @contador int
declare @ciclo as int
declare @BD varchar(100)
declare @User varchar(100)
set @ciclo =1
set @contador =(select max(id) from #UserAccountsToDelete)

WHILE(@ciclo < = @contador )
begin

set @BD = (select db from #UserAccountsToDelete where id =@ciclo)
set @User = (select [user] from #UserAccountsToDelete where id =@ciclo)

begin try
      exec ('use '+@bd+ ' drop schema ['+@user+']')
end try
begin catch
end catch
exec ('use '+@bd+ ' drop user ['+@user+']')

set @ciclo =@ciclo +1

end

drop table #UserAccountsToDelete
END
Sreejith M Nair
Sreejith M Nair
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 153
You can also use the below : Here it lists all Orphaned users on existing DB and delete Ids and associated Schema .



DECLARE @UserName varchar(100),
@SQL as varchar(max)

create table ##orphans (orphan varchar(128))
-- 1)

if substring(@@version, 1, 26) not in ('Microsoft SQL Server 2000', 'Microsoft SQL Server 2000')

insert into ##orphans select u.name as 'Orphaned Users' from sys.server_principals l right join sys.database_principals u on

l.sid = u.sid where l.sid is null and u.type not in ('A','R') and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest' and

u.name <> 'system_function_schema' and u.name <> 'sys' and not db_name() + '.' + u.name= 'msdb.MS_DataCollectorInternalUser')
else

select u.name as 'Orphaned Users' from master..sysxlogins l right join sysusers u on l.sid = u.sid where l.sid is null and

issqlrole <> 1 and isapprole <> 1 and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest' and u.name <> 'system_function_schema')



DECLARE mycurs CURSOR FOR
SELECT
orphan
FROM
##orphans

OPEN mycurs
FETCH NEXT FROM mycurs
INTO @username

WHILE @@FETCH_STATUS = 0
BEGIN

-- 2)
BEGIN TRY
EXEC sp_change_users_login @Action='update_one', @UserNamePattern=@UserName, @LoginName=@UserName
END TRY

BEGIN CATCH
print @Username + ' does not exist as a Login. Deleting. '

-- 3)
BEGIN TRY
SET @SQL = 'DROP SCHEMA [' + @UserName +']'
EXEC (@SQL)
END TRY
BEGIN CATCH
END CATCH

-- 4)
SET @SQL = 'DROP USER [' + @UserName + ']'
            Print @SQL
EXEC (@SQL)
END CATCH

FETCH NEXT FROM mycurs
INTO @username
END

CLOSE mycurs
DEALLOCATE mycurs

DROP TABLE ##orphans
mcarter1027
mcarter1027
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 194
This script works good! Thank you!!
mcarter1027
mcarter1027
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 194
For Richard: This script works good! Thank you!!
erishi
erishi
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 244
Hi,

We have the same requirement but the questions I have is I do not want to delete the schema I just want to transfer the ownership to dbo so that I can delete the user in case it owns the schema.

How would I do that. identify the user. delete the user, if the database principle owns any data change it to DBO and delete. I need to do this on all the databases.

Any help on this is appreciated. !
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search