InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 02:41:07 GMT20http://www.sqlservercentral.com/Forums/Topic964652-5-1.aspxHi All,Below query will drop all access for specified user from the user database.DECLARE C1 CURSOR FORSELECT NAME FROM MASTER.DBO.SYSDATABASESWHERE DBID > 4AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'OPEN C1FETCH NEXT FROM C1INTO @SRWHILE(@@FETCH_STATUS <> -1) BEGIN SET @CMD = 'USE [' +@SR + ']' + @NL + 'IF EXISTS (SELECT NAME FROM DBO.SYSUSERS WHERE NAME LIKE N''domain\user'')' + @NL + 'EXEC SP_REVOKEDBACCESS N''domain\user'' + @NL + PRINT 'DROPPING THE ACCOUNT FROM ' + @SR + '....' EXEC (@CMD)FETCH NEXT FROM C1INTO @SR ENDCLOSE C1DEALLOCATE C1-- @NL is CHAR(13)with some databases when users have created with username as belowlf login is : WIN\Austin then with some database it has created'Austin' only (no domail prefix) and yes login has referred to WIN\Austin.Above code works ok when both login and user on db are same but it fails when users have been created without domain prefix.I am thinking to store the output of below command in a variable.SELECT name from dbo.sysusers where SID = SUSERS_SID(N'WIN\Austin')I am not sure how to integrate this with main code. In other words If I store the output of above query then how can I pass the variable as parameter for SP_REVOKEDBACCESS @name_in_db or is there any way to execute the query as parameter for sp.Thanks in advance.Regards,AustinThu, 05 Aug 2010 13:17:26 GMTAustin_123