• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

drop user in Multiple database in sql sever

hi
 is it a simple way to write a script dropping a user whom might in multiple database in sql server
thanks
0
tristonyip
Asked:
tristonyip
1 Solution
 
sventhanCommented:
--drop user from all databases
EXEC sp_msforeachdb N'
      USE [?];

      DECLARE @user_name sysname;

      SELECT @user_name = dp.name
      FROM sys.server_principals AS sp
      JOIN sys.database_principals AS dp ON
            dp.sid = sp.sid
      WHERE sp.name = ''SomeLogin'';

      IF @user_name IS NOT NULL
      BEGIN
            SET @user_name = QUOTENAME(@user_name);
            RAISERROR(''Dropping server=%s, database=%s, user=%s'', 0, 1, @@SERVERNAME, ''?'', @user_name);
            EXEC(''DROP USER '' + @user_name);
      END';

--drop login from server
DROP LOGIN [SomeLogin];

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/53503bef-0c12-47e9-be50-6526a1159e90
0
 
tristonyipAuthor Commented:
thanks, this is perfect
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now