SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 SQL script showing half
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

raghuveer125
Constraint Violating Yak Guru

India
285 Posts

Posted - 10/12/2012 :  16:30:52  Show Profile  Reply with Quote
Hi I am getting half output if I have more than 3 logins in table


Select 'If not exists (select * from sysusers a inner join master..syslogins b on (a.sid = b.sid) and a.name = ' + '''' + a.name + '''' + ')
begin' +-- char(13) + char(9) +
'if exists(select * from sysusers where name = ' + '''' + a.name + '''' + ')
begin
exec sp_change_users_login ' + '''' +'update_one' + '''' + ',' + '''' + a.name + '''' + ',' + '''' + b.name + '''' + --char(13) + char(9) +
'end
else
begin
exec sp_grantdbaccess ' + '''' + b.name + '''' + ',' + '''' + a.name + '''' +-- char(13) + char(9) +
'end' +-- char(13) +
'end ' COLLATE Latin1_General_CI_AS --needed for case insenstive 2000 servers. Remove for 7.0 servers

from
sysusers a
left outer join master..syslogins b
on a.sid = b.sid
where
(a.issqluser = 1 or a.isntuser = 1 or a.isntgroup = 1)
and
a.name not in('guest','dbo') and a.name not like 'repl%'
group by a.name,b.name
having b.name is not null
order by 1 desc



In Love... With Me!
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000