SQL Server Performance

All databases a given user has access to (05 and 08 servers)

Discussion in 'SQL Server 2008 General DBA Questions' started by kellyrountree, Mar 24, 2010.

  1. kellyrountree New Member

    Frequently we need to remove a login across all databases on a given server for a given user login.
    Can I use Exec sp_MSforeachdb to identify all databases a given user exists on (05 and 08 servers) and how?
  2. satya Moderator

  3. kellyrountree New Member

    Thank you so much... a follow up questions if you don't mind...
    · Do I substitute the user where you are using "DBCC CKECKDB"
  4. satya.sqldba New Member

    I guess you are doing this just for user databases, you can also customize the query by using sql like belo which drops users only from user databases and not system databases
    EXEC master..sp_MSForeachdb
    'USE [?]
    IF DB_ID(''?'') > 4
    exec sp_dropuser ''username'''
  5. FrankKalis Moderator

    [quote user="satya.sqldba"]
    I guess you are doing this just for user databases, you can also customize the query by using sql like belo which drops users only from user databases and not system databases
    EXEC master..sp_MSForeachdb
    'USE [?]
    IF DB_ID(''?'') > 4
    exec sp_dropuser ''username'''
    [/quote]
    Just a minor point, but since sp_dropuser is deprecated, it might be better to use DROP USER instead. [:)]

Share This Page