|
raghuveer125
Constraint Violating Yak Guru
India
277 Posts |
 Posted - 10/12/2012 : 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! |
|
SQL script showing half
