User Search Function: Syntax Help!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

User Search Function: Syntax Help!

Author

Topic

sumwanlah
Starting Member

43 Posts

Posted - 2002-03-26 : 04:26:41

Hi!

I've written a short piece with many errors... I'm looking for anyone out there who can help me correct the syntax on this baby...

I'm trying to look through the entire server looking out for databases where a user already has access.

You see, I've got a user who is away on long-term leave, and I need to find and replace her access with another user.
I'm trying to write a script that runs through each database to find if her user login exists.
If she does, then based on the results list, I will manually remove her user login and replace it with another...

HELP!!! Hahahahahha!!!!

Here's the script:
------------------------

 declare @dbName sysname

DECLARE test_cursor CURSOR FOR
SELECT name FROM sysdatabases 
ORDER BY name

open test_cursor

fetch next from test_cursor
into @dbName

while @@FETCH_STATUS = 0
BEGIN
execute('use ' +@dbname+'')
   PRINT 'Database Now in Use: ' + @dbName
	begin
	execute('if exists (select * from '+@dbName+'.dbo.sysusers where name = "sumwanlah" and uid < 16382)')
		print 'she is here:'+@dbName	
	end
    FETCH NEXT FROM test_cursor
    INTO @dbName
END

close test_cursor
deallocate test_cursor

dsdeming

479 Posts

Posted - 2002-03-26 : 08:19:46

I think the problem is in here:

begin
execute('if exists (select * from '+@dbName+'.dbo.sysusers where
name = "sumwanlah" and uid < 16382)')
print 'she is here:'+@dbName
end

First, I don't think you'll ever get the print statement to execute reliably because the exists statement is executed dynamically but the print statement is not in the same dynamic batch. Second, because of the previous 'execute( 'use ' + @dbname )' statement, you don't need dynamic sql here anyway. The database has already been changed. You should be able to change the above code to something like:

begin
if exists (select * from dbo.sysusers where name = 'sumwanlah' and uid < 16382)
print 'she is here:'+@dbName
end

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-26 : 08:23:53

Don't have sql server here but something like this.
I have included code to drop the user (and removed the unnecessary cursor) - don't know whether you would want that.

declare @username varchar(128)
set @username = 'hisname'

declare @dbname varchar(128) ,
@uid int ,
@sql nvarchar(1000) ,
@rc int
select @uid = suid from master..syslogins where name = @username

select @dbname = ''
while exists (select * from master..sysdatabases where name > @dbname)
begin
select @dbname = min(name) from master..sysdatabases where name > @dbname
select @rc = 0
select @sql = 'select @rc = 1 from ' + @dbname' + ..sysusers where uid = ' + convert(varchar(10),@uid)
exec sp_executesql @sql, N'@rc int output, @rc output
if @rc = 1
begin
select 'user in' + @dbname
select @sql = 'exec ' + @dbname + '..sp_executesql N''drop user ' + @username + ''''
exec (@sql)

end
end

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Edited by - nr on 03/26/2002 08:25:30

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources