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
SM
sp_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:
>
> 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:
>
> 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...[vbcol=seagreen]
> 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:
|||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:
>
> 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:
>
> This doesn't make sense
> John
>