Query

• Austin_123

  SSCommitted

  Points: 1648

  More actions

  August 5, 2010 at 1:17 pm

  #97492

  Hi All,

  Below query will drop all access for specified user from the user database.

  DECLARE C1 CURSOR FOR

  SELECT NAME FROM MASTER.DBO.SYSDATABASES

  WHERE DBID > 4

  AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'

  OPEN C1

  FETCH NEXT FROM C1

  INTO @sr

  WHILE(@@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 C1

  INTO @sr

  END

  CLOSE C1

  DEALLOCATE C1

  -- @NL is CHAR(13)

  with some databases when users have created with username as below

  lf 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,

  Austin