SQL: Remove / Delete Orphan Users
As discussed in the post
SQL Delete/Drop a User from each Database, when you delete a Login from SQL Server it does not remove them from the databases they have explicit permissions on. If a user login is removed, and they are not removed from the database you would have orphan users with permission to the database. The stored procedure listed will remove (delete) orphan datatabase user logins.
1 USE [master]
2 GO
3 SET ANSI_NULLS ON
4 GO
5 SET QUOTED_IDENTIFIER ON
6 GO
7
8 create procedure [dbo].[sp_DeleteOrphanUsers]
9 AS
10
11 -- revoke orphan user
12 declare @cmd as varchar(1000)
13 declare @uname as sysname
14
15 DECLARE my_cur INSENSITIVE SCROLL CURSOR
16 FOR
17 -- orphan user
18 select u.name from master..syslogins l right join
19 sysusers u on l.sid = u.sid
20 where l.sid is null and issqlrole <> 1 and isapprole <> 1
21 and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest' and u.name <> 'sys'
22 and u.name <> 'system_function_schema')
23 order by u.name
24
25 open my_cur
26
27 WHILE (1 = 1)
28 begin
29 FETCH FROM my_cur INTO @uname
30 IF @@fetch_status <> 0
31 BREAK
32 set @cmd = 'exec sp_revokedbaccess ''' + rtrim(@uname) + ''''
33 print @cmd
34 exec (@cmd)
35
36 end
37 DEALLOCATE my_cur
38
39 return(0)
The procedure would be executed like the following:
1 use [databasename] /* where databasename is the name of the database to run the procedure on */
2 exec sp_DeleteOrphanusers
Labels: Code, How-To, SQL
AddrMon
Edelklaus Wednesday, November 12, 2008 8:57:00 AM 
October 2005
Subscribe!