drop login Stored Procedure

hi ,
I would like to drop all invalid logins in my 2000 and 2005 environment . Do
we a SP that runs on both 2000 and 2005 to drop the logins .
--
Thanks & Regards
SMsp_droplogin?
"moharil" <sid_m15@.yahoo.com> wrote in message
news:2EAA8272-02A0-43E6-ABA0-042B5F0E355B@.microsoft.com...
> hi ,
> I would like to drop all invalid logins in my 2000 and 2005 environment .
> Do
> we a SP that runs on both 2000 and 2005 to drop the logins .
> --
> Thanks & Regards
> SM|||Hi
"moharil" wrote:
> hi ,
> I would like to drop all invalid logins in my 2000 and 2005 environment . Do
> we a SP that runs on both 2000 and 2005 to drop the logins .
> --
> Thanks & Regards
> SM
sp_dropLogin will work on SQL 2005 although DROP LOGIN is preferred.
John|||Thanks but I knew drop login . sorry I should had framed it in another manner
. I am looking for a generalized script/sp that will run on all sql server
and drop the invalid login and send us a mail saying the login has been
dropped.
--
Thanks & Regards
Sid
"John Bell" wrote:
> Hi
> "moharil" wrote:
> > hi ,
> > I would like to drop all invalid logins in my 2000 and 2005 environment . Do
> > we a SP that runs on both 2000 and 2005 to drop the logins .
> > --
> > Thanks & Regards
> > SM
> sp_dropLogin will work on SQL 2005 although DROP LOGIN is preferred.
> John|||i have 2 sybase sp' s that run using the below listed SP i need something
similar that runs on sql server 2000-05
******************sp_drop_login_completely ****************
CREATE PROC sp_drop_login_completely @.login varchar(30)
as
declare @.msg varchar(20),
@.cnt int,
@.ret_code int,
@.db varchar(30),
@.status smallint,
@.proc_name varchar(92),
@.grp_nm varchar(30),
@.aliased_user_nm varchar(30)
select @.status = 0
select @.ret_code = 0
create table #display (db_nm varchar(30), grp_nm varchar(30) null,
aliased_user_nm varchar(30) null)
/*
** Delete user/alias from databases for this login
*/
declare databases_crs cursor for
select name, status=status & 1024 from master..sysdatabases
where
status & 1024 != 1024 /* not read only */
and status & 256 != 256 /* not suspect */
and status & 44 != 44 /* not in for load status */
for read only
open databases_crs
fetch databases_crs into @.db, @.status
WHILE (@.@.sqlstatus = 0)
BEGIN
select @.grp_nm = null, @.aliased_user_nm = null
select @.proc_name = @.db + "..sp_phh_model_group_nm"
exec @.ret_code = @.proc_name @.login, @.grp_nm output, @.aliased_user_nm
output
IF @.grp_nm is not null
BEGIN
insert #display values (@.db, @.grp_nm, @.aliased_user_nm)
select @.proc_name = @.db + "..sp_dropuser"
exec @.ret_code = @.proc_name @.login
if @.ret_code != 0
print 'Error: Unable to drop Sybase user %1!, on database %2! ',
@.login, @.db
END
IF @.aliased_user_nm is not null
BEGIN
insert #display values (@.db, @.grp_nm, @.aliased_user_nm)
select @.proc_name = @.db + "..sp_dropalias"
exec @.ret_code = @.proc_name @.login,"force"
if (@.ret_code != 0)
print 'Error: Unable to drop Sybase alias %1!, on database %2!
', @.login, @.db
END
select @.status = 0
fetch databases_crs into @.db, @.status
select @.status = @.status
END
close databases_crs
deallocate cursor databases_crs
/*
** Now that @.login isn't in any databases, drop the login
*/
if suser_id(@.login) is not null
BEGIN
exec sp_droplogin @.login
if (@.@.error != 0)
print 'Error: Unable to drop Sybase login %1!', @.login
END
/*
** Show where the user was
*/
print 'Login: %1! was removed from the following databases', @.login
select db_nm, @.login as login_nm, isnull(grp_nm,'') as grp_nm,
isnull(aliased_user_nm,'') as aliased_user_nm from #display
return
go
*************sp_phh_model_group_nm*******************
create proc sp_model_group_nm
@.model_to_follow varchar(30),
@.grp_nm_model_is_in varchar(30) output,
@.aliased_user_nm varchar(30) output
as
select @.grp_nm_model_is_in = g.name
from sysusers u, sysusers g,
master.dbo.syslogins m
where u.suid *= m.suid
and u.gid *= g.uid
and u.name = @.model_to_follow
and u.uid <= 16383 and u.uid != 0
select @.aliased_user_nm = (select b.name from sysusers b where a.altsuid =b.suid)
from sysalternates a
where suser_name(a.suid) = @.model_to_follow
return
go
*******************************
--
Thanks & Regards
Sid
"John Bell" wrote:
> Hi
> "moharil" wrote:
> > hi ,
> > I would like to drop all invalid logins in my 2000 and 2005 environment . Do
> > we a SP that runs on both 2000 and 2005 to drop the logins .
> > --
> > Thanks & Regards
> > SM
> sp_dropLogin will work on SQL 2005 although DROP LOGIN is preferred.
> John|||Hi
Run this query to identify ophaned logins and then create a script
(cursor with sp_droplogin ) to delete them
select sl.name
from master..syslogins sl
join sysusers su on sl.sid<>sl.sid
"moharil" <sid_m15@.yahoo.com> wrote in message
news:6E568992-1A2F-49BE-B2C3-BB9A532D57C2@.microsoft.com...
> Thanks but I knew drop login . sorry I should had framed it in another
> manner
> . I am looking for a generalized script/sp that will run on all sql server
> and drop the invalid login and send us a mail saying the login has been
> dropped.
> --
> Thanks & Regards
> Sid
>
> "John Bell" wrote:
>> Hi
>> "moharil" wrote:
>> > hi ,
>> > I would like to drop all invalid logins in my 2000 and 2005 environment
>> > . Do
>> > we a SP that runs on both 2000 and 2005 to drop the logins .
>> > --
>> > Thanks & Regards
>> > SM
>> sp_dropLogin will work on SQL 2005 although DROP LOGIN is preferred.
>> John|||Uri
"Uri Dimant" wrote:
> Hi
> Run this query to identify ophaned logins and then create a script
> (cursor with sp_droplogin ) to delete them
> select sl.name
> from master..syslogins sl
> join sysusers su on sl.sid<>sl.sid
>
This doesn't make sense
John|||can we modify the listed sybase SP's to run on sql servers ?
--
Thanks & Regards
Sid
"John Bell" wrote:
> Uri
> "Uri Dimant" wrote:
> > Hi
> > Run this query to identify ophaned logins and then create a script
> > (cursor with sp_droplogin ) to delete them
> >
> > select sl.name
> > from master..syslogins sl
> > join sysusers su on sl.sid<>sl.sid
> >
> This doesn't make sense
> John
>|||Hi
"moharil" wrote:
> i have 2 sybase sp' s that run using the below listed SP i need something
> similar that runs on sql server 2000-05
> ******************sp_drop_login_completely ****************
> CREATE PROC sp_drop_login_completely @.login varchar(30)
> as
> declare @.msg varchar(20),
> @.cnt int,
> @.ret_code int,
> @.db varchar(30),
> @.status smallint,
> @.proc_name varchar(92),
> @.grp_nm varchar(30),
> @.aliased_user_nm varchar(30)
> select @.status = 0
> select @.ret_code = 0
> create table #display (db_nm varchar(30), grp_nm varchar(30) null,
> aliased_user_nm varchar(30) null)
> /*
> ** Delete user/alias from databases for this login
> */
> declare databases_crs cursor for
> select name, status=status & 1024 from master..sysdatabases
> where
> status & 1024 != 1024 /* not read only */
> and status & 256 != 256 /* not suspect */
> and status & 44 != 44 /* not in for load status */
> for read only
> open databases_crs
> fetch databases_crs into @.db, @.status
> WHILE (@.@.sqlstatus = 0)
> BEGIN
> select @.grp_nm = null, @.aliased_user_nm = null
> select @.proc_name = @.db + "..sp_phh_model_group_nm"
> exec @.ret_code = @.proc_name @.login, @.grp_nm output, @.aliased_user_nm
> output
> IF @.grp_nm is not null
> BEGIN
> insert #display values (@.db, @.grp_nm, @.aliased_user_nm)
> select @.proc_name = @.db + "..sp_dropuser"
> exec @.ret_code = @.proc_name @.login
> if @.ret_code != 0
> print 'Error: Unable to drop Sybase user %1!, on database %2! ',
> @.login, @.db
> END
> IF @.aliased_user_nm is not null
> BEGIN
> insert #display values (@.db, @.grp_nm, @.aliased_user_nm)
> select @.proc_name = @.db + "..sp_dropalias"
> exec @.ret_code = @.proc_name @.login,"force"
> if (@.ret_code != 0)
> print 'Error: Unable to drop Sybase alias %1!, on database %2!
> ', @.login, @.db
> END
> select @.status = 0
> fetch databases_crs into @.db, @.status
> select @.status = @.status
> END
> close databases_crs
> deallocate cursor databases_crs
> /*
> ** Now that @.login isn't in any databases, drop the login
> */
> if suser_id(@.login) is not null
> BEGIN
> exec sp_droplogin @.login
> if (@.@.error != 0)
> print 'Error: Unable to drop Sybase login %1!', @.login
> END
> /*
> ** Show where the user was
> */
> print 'Login: %1! was removed from the following databases', @.login
> select db_nm, @.login as login_nm, isnull(grp_nm,'') as grp_nm,
> isnull(aliased_user_nm,'') as aliased_user_nm from #display
> return
> go
> *************sp_phh_model_group_nm*******************
> create proc sp_model_group_nm
> @.model_to_follow varchar(30),
> @.grp_nm_model_is_in varchar(30) output,
> @.aliased_user_nm varchar(30) output
> as
> select @.grp_nm_model_is_in = g.name
> from sysusers u, sysusers g,
> master.dbo.syslogins m
> where u.suid *= m.suid
> and u.gid *= g.uid
> and u.name = @.model_to_follow
> and u.uid <= 16383 and u.uid != 0
> select @.aliased_user_nm = (select b.name from sysusers b where a.altsuid => b.suid)
> from sysalternates a
> where suser_name(a.suid) = @.model_to_follow
> return
> go
> *******************************
> --
> Thanks & Regards
> Sid
Look at using the DATABASE_PROPERTY function instead of checking the status,
and use LEFT JOIN instead of *= join syntax. sysalternates does not exist in
SQL Server 2000/2005 and you can use the columns isSQLRole, isAppRole etc in
sysusers to determine whether the user is a role or not.
There is an assumption that the users associated to the login do not own any
objects, check sysobjects and information_schema.schemata would be able to
deterine these.
John|||Hi John
> This doesn't make sense
What did you mean?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BE169473-B166-4A4D-8582-29E90D6F735B@.microsoft.com...
> Uri
> "Uri Dimant" wrote:
>> Hi
>> Run this query to identify ophaned logins and then create a script
>> (cursor with sp_droplogin ) to delete them
>> select sl.name
>> from master..syslogins sl
>> join sysusers su on sl.sid<>sl.sid
> This doesn't make sense
> John
>|||Hi Uri
The most obvious problem would be comparing sl.sid <> sl.sid ! But even if
you change to su.sid there will be logins that are associated with other
users but not the current one and a login may be associated with users in
another database and not the current one! If you are looking for orphaned
users than you would take the sid from sysusers and make sure it wasn't in
syslogins which is the opposite way around to what you have (and would
require an outer join on the sids being the same), but I don't think that is
what the OP wants!
John
"Uri Dimant" wrote:
> Hi John
> > This doesn't make sense
>
> What did you mean?
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:BE169473-B166-4A4D-8582-29E90D6F735B@.microsoft.com...
> >
> > Uri
> >
> > "Uri Dimant" wrote:
> >
> >> Hi
> >> Run this query to identify ophaned logins and then create a script
> >> (cursor with sp_droplogin ) to delete them
> >>
> >> select sl.name
> >> from master..syslogins sl
> >> join sysusers su on sl.sid<>sl.sid
> >>
> >
> > This doesn't make sense
> >
> > John
> >
>
>|||Hi
"moharil" wrote:
> can we modify the listed sybase SP's to run on sql servers ?
> --
> Thanks & Regards
> Sid
>
I gave a list of changes in my other reply, you would need to work through
the code and test it against both SQL 2000 and SQL 2005.
John|||Hi John
Yep , I was mistaken , it means sl.sid <> su.sid. As you know when yopu
create a new login (without specifying SID) ,sql server generates a new
(randomaly) SID. So you are saying that if I restore database (SQL
Authentication) which has a user mapped to the login on the 'new' server
it could be that user's SID(of restored db) will be match to 'some' login in
the'new' server , do I understand you properly?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FE5E5E01-2BC0-4B51-88EF-2859E52B58D4@.microsoft.com...
> Hi Uri
> The most obvious problem would be comparing sl.sid <> sl.sid ! But even if
> you change to su.sid there will be logins that are associated with other
> users but not the current one and a login may be associated with users in
> another database and not the current one! If you are looking for orphaned
> users than you would take the sid from sysusers and make sure it wasn't in
> syslogins which is the opposite way around to what you have (and would
> require an outer join on the sids being the same), but I don't think that
> is
> what the OP wants!
> John
>
> "Uri Dimant" wrote:
>> Hi John
>> > This doesn't make sense
>>
>> What did you mean?
>>
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:BE169473-B166-4A4D-8582-29E90D6F735B@.microsoft.com...
>> >
>> > Uri
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Hi
>> >> Run this query to identify ophaned logins and then create a script
>> >> (cursor with sp_droplogin ) to delete them
>> >>
>> >> select sl.name
>> >> from master..syslogins sl
>> >> join sysusers su on sl.sid<>sl.sid
>> >>
>> >
>> > This doesn't make sense
>> >
>> > John
>> >
>>|||Hi Uri
"Uri Dimant" wrote:
> Hi John
> Yep , I was mistaken , it means sl.sid <> su.sid. As you know when yopu
> create a new login (without specifying SID) ,sql server generates a new
> (randomaly) SID. So you are saying that if I restore database (SQL
> Authentication) which has a user mapped to the login on the 'new' server
> it could be that user's SID(of restored db) will be match to 'some' login in
> the'new' server , do I understand you properly?
>
I am not sure how sids are created to say if there is some element of them
that will not allow them to match other sids generated on a different
instance/machine. Orphaned users can be retrieved using sp_change_users_login
'report' so there is no real need to write your own code for finding them.
They can then be mapped by calling sp_change_users_login with either
update_one or auto_fix as the first parameter.
John|||Hi John
Its not always the way ,especially for non-experienced people to use
sp_change_users_login stored procedure which works very well as you pointed
So they prefer drop user or even login
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:5AEEE5A3-AF74-4D3D-9B67-E5C0A44BDC94@.microsoft.com...
> Hi Uri
> "Uri Dimant" wrote:
>> Hi John
>> Yep , I was mistaken , it means sl.sid <> su.sid. As you know when yopu
>> create a new login (without specifying SID) ,sql server generates a new
>> (randomaly) SID. So you are saying that if I restore database (SQL
>> Authentication) which has a user mapped to the login on the 'new' server
>> it could be that user's SID(of restored db) will be match to 'some' login
>> in
>> the'new' server , do I understand you properly?
> I am not sure how sids are created to say if there is some element of them
> that will not allow them to match other sids generated on a different
> instance/machine. Orphaned users can be retrieved using
> sp_change_users_login
> 'report' so there is no real need to write your own code for finding them.
> They can then be mapped by calling sp_change_users_login with either
> update_one or auto_fix as the first parameter.
> John|||Hi Uri
"Uri Dimant" wrote:
> Hi John
> Its not always the way ,especially for non-experienced people to use
> sp_change_users_login stored procedure which works very well as you pointed
> So they prefer drop user or even login
But then they have an issue with re-granting permission which if the used
sp_change_users_login does not require! I tend to find that the main reason
that people have for not using it they don't know about it, rather than ease
of use!
John|||i was able to modify the SP and i am able to run the SP the logins / user
gets deleted from the sql server . currenly i am working on writing a .sh
script on windows that will call the SP and send mails to our group saying
the login has been dropped
--
Thanks
SM
"John Bell" wrote:
> Hi
> "moharil" wrote:
> > i have 2 sybase sp' s that run using the below listed SP i need something
> > similar that runs on sql server 2000-05
> > ******************sp_drop_login_completely ****************
> > CREATE PROC sp_drop_login_completely @.login varchar(30)
> > as
> > declare @.msg varchar(20),
> > @.cnt int,
> > @.ret_code int,
> > @.db varchar(30),
> > @.status smallint,
> > @.proc_name varchar(92),
> > @.grp_nm varchar(30),
> > @.aliased_user_nm varchar(30)
> > select @.status = 0
> > select @.ret_code = 0
> > create table #display (db_nm varchar(30), grp_nm varchar(30) null,
> > aliased_user_nm varchar(30) null)
> > /*
> > ** Delete user/alias from databases for this login
> > */
> > declare databases_crs cursor for
> > select name, status=status & 1024 from master..sysdatabases
> > where
> > status & 1024 != 1024 /* not read only */
> > and status & 256 != 256 /* not suspect */
> > and status & 44 != 44 /* not in for load status */
> > for read only
> > open databases_crs
> > fetch databases_crs into @.db, @.status
> > WHILE (@.@.sqlstatus = 0)
> > BEGIN
> > select @.grp_nm = null, @.aliased_user_nm = null
> > select @.proc_name = @.db + "..sp_phh_model_group_nm"
> > exec @.ret_code = @.proc_name @.login, @.grp_nm output, @.aliased_user_nm
> > output
> > IF @.grp_nm is not null
> > BEGIN
> > insert #display values (@.db, @.grp_nm, @.aliased_user_nm)
> > select @.proc_name = @.db + "..sp_dropuser"
> > exec @.ret_code = @.proc_name @.login
> > if @.ret_code != 0
> > print 'Error: Unable to drop Sybase user %1!, on database %2! ',
> > @.login, @.db
> > END
> > IF @.aliased_user_nm is not null
> > BEGIN
> > insert #display values (@.db, @.grp_nm, @.aliased_user_nm)
> > select @.proc_name = @.db + "..sp_dropalias"
> > exec @.ret_code = @.proc_name @.login,"force"
> > if (@.ret_code != 0)
> > print 'Error: Unable to drop Sybase alias %1!, on database %2!
> > ', @.login, @.db
> > END
> > select @.status = 0
> > fetch databases_crs into @.db, @.status
> > select @.status = @.status
> > END
> > close databases_crs
> > deallocate cursor databases_crs
> > /*
> > ** Now that @.login isn't in any databases, drop the login
> > */
> > if suser_id(@.login) is not null
> > BEGIN
> > exec sp_droplogin @.login
> > if (@.@.error != 0)
> > print 'Error: Unable to drop Sybase login %1!', @.login
> > END
> > /*
> > ** Show where the user was
> > */
> > print 'Login: %1! was removed from the following databases', @.login
> > select db_nm, @.login as login_nm, isnull(grp_nm,'') as grp_nm,
> > isnull(aliased_user_nm,'') as aliased_user_nm from #display
> > return
> > go
> > *************sp_phh_model_group_nm*******************
> > create proc sp_model_group_nm
> > @.model_to_follow varchar(30),
> > @.grp_nm_model_is_in varchar(30) output,
> > @.aliased_user_nm varchar(30) output
> > as
> >
> > select @.grp_nm_model_is_in = g.name
> > from sysusers u, sysusers g,
> > master.dbo.syslogins m
> > where u.suid *= m.suid
> > and u.gid *= g.uid
> > and u.name = @.model_to_follow
> > and u.uid <= 16383 and u.uid != 0
> >
> > select @.aliased_user_nm = (select b.name from sysusers b where a.altsuid => > b.suid)
> > from sysalternates a
> > where suser_name(a.suid) = @.model_to_follow
> >
> > return
> > go
> > *******************************
> >
> > --
> > Thanks & Regards
> > Sid
> Look at using the DATABASE_PROPERTY function instead of checking the status,
> and use LEFT JOIN instead of *= join syntax. sysalternates does not exist in
> SQL Server 2000/2005 and you can use the columns isSQLRole, isAppRole etc in
> sysusers to determine whether the user is a role or not.
> There is an assumption that the users associated to the login do not own any
> objects, check sysobjects and information_schema.schemata would be able to
> deterine these.
> John|||Hi
That is great!!
John
"moharil" wrote:
> i was able to modify the SP and i am able to run the SP the logins / user
> gets deleted from the sql server . currenly i am working on writing a .sh
> script on windows that will call the SP and send mails to our group saying
> the login has been dropped
> --
> Thanks
> SM
>
> "John Bell" wrote:
> > Hi
> >
> > "moharil" wrote:
> >
> > > i have 2 sybase sp' s that run using the below listed SP i need something
> > > similar that runs on sql server 2000-05
> > > ******************sp_drop_login_completely ****************
> > > CREATE PROC sp_drop_login_completely @.login varchar(30)
> > > as
> > > declare @.msg varchar(20),
> > > @.cnt int,
> > > @.ret_code int,
> > > @.db varchar(30),
> > > @.status smallint,
> > > @.proc_name varchar(92),
> > > @.grp_nm varchar(30),
> > > @.aliased_user_nm varchar(30)
> > > select @.status = 0
> > > select @.ret_code = 0
> > > create table #display (db_nm varchar(30), grp_nm varchar(30) null,
> > > aliased_user_nm varchar(30) null)
> > > /*
> > > ** Delete user/alias from databases for this login
> > > */
> > > declare databases_crs cursor for
> > > select name, status=status & 1024 from master..sysdatabases
> > > where
> > > status & 1024 != 1024 /* not read only */
> > > and status & 256 != 256 /* not suspect */
> > > and status & 44 != 44 /* not in for load status */
> > > for read only
> > > open databases_crs
> > > fetch databases_crs into @.db, @.status
> > > WHILE (@.@.sqlstatus = 0)
> > > BEGIN
> > > select @.grp_nm = null, @.aliased_user_nm = null
> > > select @.proc_name = @.db + "..sp_phh_model_group_nm"
> > > exec @.ret_code = @.proc_name @.login, @.grp_nm output, @.aliased_user_nm
> > > output
> > > IF @.grp_nm is not null
> > > BEGIN
> > > insert #display values (@.db, @.grp_nm, @.aliased_user_nm)
> > > select @.proc_name = @.db + "..sp_dropuser"
> > > exec @.ret_code = @.proc_name @.login
> > > if @.ret_code != 0
> > > print 'Error: Unable to drop Sybase user %1!, on database %2! ',
> > > @.login, @.db
> > > END
> > > IF @.aliased_user_nm is not null
> > > BEGIN
> > > insert #display values (@.db, @.grp_nm, @.aliased_user_nm)
> > > select @.proc_name = @.db + "..sp_dropalias"
> > > exec @.ret_code = @.proc_name @.login,"force"
> > > if (@.ret_code != 0)
> > > print 'Error: Unable to drop Sybase alias %1!, on database %2!
> > > ', @.login, @.db
> > > END
> > > select @.status = 0
> > > fetch databases_crs into @.db, @.status
> > > select @.status = @.status
> > > END
> > > close databases_crs
> > > deallocate cursor databases_crs
> > > /*
> > > ** Now that @.login isn't in any databases, drop the login
> > > */
> > > if suser_id(@.login) is not null
> > > BEGIN
> > > exec sp_droplogin @.login
> > > if (@.@.error != 0)
> > > print 'Error: Unable to drop Sybase login %1!', @.login
> > > END
> > > /*
> > > ** Show where the user was
> > > */
> > > print 'Login: %1! was removed from the following databases', @.login
> > > select db_nm, @.login as login_nm, isnull(grp_nm,'') as grp_nm,
> > > isnull(aliased_user_nm,'') as aliased_user_nm from #display
> > > return
> > > go
> > > *************sp_phh_model_group_nm*******************
> > > create proc sp_model_group_nm
> > > @.model_to_follow varchar(30),
> > > @.grp_nm_model_is_in varchar(30) output,
> > > @.aliased_user_nm varchar(30) output
> > > as
> > >
> > > select @.grp_nm_model_is_in = g.name
> > > from sysusers u, sysusers g,
> > > master.dbo.syslogins m
> > > where u.suid *= m.suid
> > > and u.gid *= g.uid
> > > and u.name = @.model_to_follow
> > > and u.uid <= 16383 and u.uid != 0
> > >
> > > select @.aliased_user_nm = (select b.name from sysusers b where a.altsuid => > > b.suid)
> > > from sysalternates a
> > > where suser_name(a.suid) = @.model_to_follow
> > >
> > > return
> > > go
> > > *******************************
> > >
> > > --
> > > Thanks & Regards
> > > Sid
> >
> > Look at using the DATABASE_PROPERTY function instead of checking the status,
> > and use LEFT JOIN instead of *= join syntax. sysalternates does not exist in
> > SQL Server 2000/2005 and you can use the columns isSQLRole, isAppRole etc in
> > sysusers to determine whether the user is a role or not.
> >
> > There is an assumption that the users associated to the login do not own any
> > objects, check sysobjects and information_schema.schemata would be able to
> > deterine these.
> >
> > John