Drop Login only if Not in any database

• Joel Paula-126111

  SSC Enthusiast

  Points: 106

  More actions

  May 7, 2004 at 7:16 am

  #85008

  I would like to Drop a user I have created in code, but only if the user is not being used in another database.

  I would like to do it with T-SQL. SOmething like the second resultset in sp_helplogins would be great, but I don't know how to use a 2nd resultset in a T-SQL statement.

  Thanks for your help.

  JOEL

• Antares686

  SSC Guru

  Points: 125444

  More actions

  May 7, 2004 at 9:48 am

  #505680

  This should help as a starting point

   

  set nocount on

  if object_id('tempdb..#tbl_users') is not null

  drop table #tbl_users

  create table #tbl_Users (

   [name] [sysname] NOT NULL,

   [sid] [varbinary](85) NOT NULL,

   [dbname] [varchar](7000) NULL

  )

  INSERT #tbl_Users ([name], sid) select [NAME], SID from syslogins where [name] is not null and

  1 not in (sysadmin, securityadmin, serveradmin, setupadmin, processadmin, diskadmin, dbcreator) -- This line handles server roles

  EXEC sp_msforeachdb '

  UPDATE

   LU

  SET

   [dbname] = isnull([dbname] + '', '','''') + ''?''

  FROM

   #tbl_Users LU

  INNER JOIN

   [?]..sysusers SU

  ON

   SU.SID = LU.SID'

  -- If dbnbame field is NULL then no database references the login.

  -- However this doesn't mean you couldn't have an orphaned account that needs to be corrected.

  -- ALso does not cover remote logins or logins attached to linked servers.

  select * from #tbl_Users where dbname is null