locked
DB Users without Login RRS feed

  • Question

  • I was reading this article and this article.

    I'm trying to figure out if there's a way to run through each DB to query (t-sql) DB Users without a SQL Server Login - it could be:

    1) from a recently restored DB and the users were restored without a corresponding SQL Server Logins.

    2) Or it could be Logins were dropped without dropping DB Users (orphaned)

    3) Or it could be Users without Login.

    Any reference is helpful.

    Thank you.


    Tuesday, May 2, 2017 4:58 PM

All replies

  • Hello,

    DB users without login works only for Contained Databases, not for regular databases.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, May 2, 2017 5:20 PM
  • If you are trying to identify 'orphaned' database users (those without a corresponding SQL Login) for purposes of removing them from the databases, you can create usp_remove_orphan_users with following script and execute it to generated a drop user script:

    create proc usp_remove_orphan_users as 
    
    -- Written by: Gregory A. Larsen  
    -- Script to modify database owner, and remove all users that 
    -- are not mapped to logins.
    
    set nocount on
    
    -- Section 1: Create temporary table to hold databases to process 
    -- drop table if it already exists
    
    if (select object_id('tempdb..##dbnames')) is not null
      drop table ##dbnames
    
    -- Create table to hold databases to process
    
    create table ##dbnames (dbname varchar(128))
    
    -- Section 2: Determine what databases have orphan users
    
    exec master.dbo.sp_MSforeachdb 'insert into ##dbnames select ''?'' from master..syslogins 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'') 
    	having count(*) > 0'
    
    -- Section 3: Create local variables needed
    declare @CNT int
    declare @name char(128)
    declare @sid  varbinary(85)
    declare @cmd nchar(4000)
    declare @c int
    declare @hexnum char(100)
    declare @db varchar(100) 
    
    -- Section 5: Process through each database and remove orphan users
    select @cnt=count(*) from ##DBNAMES
    While @CNT > 0
    begin
    
    -- get the name of the top database
      select top 1 @db=dbname from ##DBNAMES
    
    -- delete top database 
      delete from ##DBNAMES where dbname = @db
    
    -- Build and execute command to determine if DBO is not mapped to login
      set @cmd = 'select @cnt = count(*) from master..syslogins l right join ' + 
                 rtrim(@db) + '..sysusers u on l.sid = u.sid' + 
                 ' where l.sid is null and u.name = ''DBO'''
      exec sp_executesql @cmd,N'@cnt int out',@cnt out
    
    -- if DB is not mapped to login that exists map DBO to SA
      if @cnt = 1
      begin
        print 'exec ' + @db + '..sp_changedbowner ''SA'''  
    
        -- exec sp_changedbowner 'SA'
      end -- if @cnt = 1
    
    -- drop table if it already exists
    if (select object_id('tempdb..##orphans')) is not null
      drop table ##orphans
    
    -- Create table to hold orphan users
    create table ##orphans (orphan varchar(128))
    
    -- Build and execute command to get list of all orphan users (Windows and SQL Server)
    -- for current database being processed
       set @cmd = 'insert into ##orphans select u.name from master..syslogins l right join ' + 
                  rtrim(@db) + '..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'')'
       exec (@cmd)
    
    -- Are there orphans
      select @cnt = count(*) from ##orphans
      WHILE @cnt > 0 
      BEGIN
    
    -- get top orphan
      select top 1 @name= orphan from ##orphans
    
    -- delete top orphan
      delete from ##orphans where orphan = @name
    
    -- Build command to drop user from database.
        set @cmd = 'exec ' + rtrim(@db) + '..sp_revokedbaccess ''' + rtrim(@name) + ''''
        print @cmd
    
        --exec (@cmd)
    
    -- are there orphans left
        select @cnt = count(*) from ##orphans  
      end --  WHILE @cnt > 0
    
    -- are the still databases to process
    select @cnt=count(*) from ##dbnames
    end -- while @cnt > 0
    
    -- Remove temporary tables
    drop table ##dbnames, ##orphans
    

    Copy-paste the generated script to another query window and execute it to weed out all the non-working database users (orphans) from databases to keep them clean.

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    Tuesday, May 2, 2017 5:59 PM
  • Thank you Philfactor. That is helpful. I tested it in my dev and it generated a bunch of 'sys' (the built-in and disabled when new db is created); is that necessary to revoke as well?

    Thanks

    Tuesday, May 2, 2017 6:24 PM
  • No, you can ignore or comment out the sys and dbo users, as these are default users that exist in almost all databases and cannot be dropped.

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    Tuesday, May 2, 2017 8:20 PM
  • DB users without login works only for Contained Databases <https://docs.microsoft.com/en-us/sql/relational-databases/databases/contained-databases>, not for regular databases.

    No, this is incorrect. CREATE USER xyz WITHOUT LOGIN is possible in any database, and has been so since SQL 2005.

    Tuesday, May 2, 2017 9:19 PM