Query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » Query

Query

Rate Topic

Display Mode

Topic Options

Author

Message

Austin_123

Posted Thursday, August 05, 2010 1:17 PM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, May 10, 2013 10:37 AM
Points: 447, Visits: 366

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 »

Permissions