October 6, 2008 at 5:50 am
Hi,
I need one help on the below information,
I am using the belwo script to find out the above infromation in SQL Serve 20005
create table #test(DatabaseName varchar(100),DatabaseUser varchar(100),RevokeAccess varchar(1000),DropLogin varchar(1000),AddLogin varchar(1000),GrantAccess varchar(1000))
declare @name varchar(100)
declare @db_cur cursor
set @db_cur = cursor
for
select name from sys.databases where name not in ('master','model','tempdb','msdb')
open @db_cur
fetch next from @db_cur into @name
while @@fetch_status = 0
begin
exec('use['+@name+']
insert #test(DatabaseName,DatabaseUser)
select '''+@name+''',u.name
from sys.database_principals u
left join sys.server_principals l on u.sid = l.sid
where u.type <> ''R'' and l.name is null;')
fetch next from @db_cur into @name
end
select * from #test where DatabaseUser not in ('guest','INFORMATION_SCHEMA','sys','dbo')
close @db_cur
deallocate @db_cur
drop table #test
Could you please let me know what is the replacement script which can be used in sql server 2000,Please give a script
October 6, 2008 at 6:30 am
Replace sys.databases with sysdatabases. Replace sys.server_principals with syslogins and replace sys.database_principals with sysusers
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2008 at 9:47 pm
thanks,still i ahev soem issues,i think i can correct it
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply