Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Expand / Collapse
Author
Message
Posted Thursday, August 5, 2010 1:17 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 9:54 AM
Points: 448, Visits: 405
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
Post #964652
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse