SQL script showing half - SQL Server Forums

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)

SQL script showing half

Author

Topic

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2012-10-12 : 16:30:52

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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources