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
>
Showing posts with label environment. Show all posts
Showing posts with label environment. Show all posts
Monday, March 19, 2012
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
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
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 manne
r
. 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_complete
ly ****************
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_complete
ly ****************
> 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 i
n
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
>
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 manne
r
. 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_complete
ly ****************
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_complete
ly ****************
> 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 i
n
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
>
Sunday, February 26, 2012
Drives in a cluster environment
Hi,
I have a SAN and configuring a cluster on SQL 2005. I initially created a Quorum drive when setting up the cluster and now added 4 more drives to the physical node but when I try to install SQL that drive cannot be located.
Do we need to create all the drives when installing the cluster or what is the way to add the drives later on.
Thanks
Anup
I remember that adding a drive for sql requires the drive to be added to the resource group, I assume you have to create a resource group prior to install?|||It's one thing to add them locally - did you add them to the cluster via Cluster Admin as well? There's a procedure for adding drives after the cluster is already configured. Are they seen in CluAdmin? If not, you did not add them properly.
You also didn't mention MS DTC. That needs its own drive now as well in its own group with an IP, name, and DTC resource.
|||Thanks All.
With some troubleshooting and tips from this forums the cluster is up and running with SQL 2005. However I do not understand whi we need a seperate drive for MSDTC cant we share the quorum drive to do this. Can you explain how to size this like perf issues, disks needed etc.
Thanks
|||
For failover clusters, you always need to think in terms of "units of failover". If you put the MSDTC data on the same drive as the quorum, you would need to tie MSDTC to the cluster service. You then create dependancies between MSDTC and the cluster service that shouldn't be there.
In general, you want to think in terms of a service and its associated resources. That bundle needs to be independant from all other bundles on the cluster so that it can move from node to node independant of other bundles (resource groups).
For disk storage, the unit of availability is the physical disk (or LUN in the case of a SAN array). You can't have one partition of a disk mounted to one node and another partition mounted to another node.
So, it's not a matter of capacity or throughput or perf issues, it is an availability issue.
|||Kevin Farlee wrote:
For failover clusters, you always need to think in terms of "units of failover". If you put the MSDTC data on the same drive as the quorum, you would need to tie MSDTC to the cluster service. You then create dependancies between MSDTC and the cluster service that shouldn't be there.
Just to reinforce - this is not a recommended configuration. It only existed with Windows 2000 because of comclust and that has (thankfully) been taken away in W2K3. Always put MS DTC in a separate group.
In the cases where MS DTC is heavily used (such as BTS), it could potentially affect the availability of the quorum disk (i.e. if it gets filled up with DTC log). You don't want to go there.
|||
Assuming you added the new drives as a disk resource in cluster manager, did you also make those new drives a Dependency of SQL Server? If not, SQL Server will not be able to see them.
|||
Thanks. Now I am getting to understand the concepts. However one last question if I have a active/active cluster then do i need to setup 2 MSDTC groups one in each node?
I appreciate all the help throughout .
|||
No, one DTC per cluster. It is shared with everything else in the cluster.
You will need separate dedicated disks and such for your other SQL instance(s) though.
Driver Not Capable - Error
I'm getting this error and I don't know where to begin. Our environment is
SQL Server 2000 sp3a on Win Server 2003.
I get the error when running an insert statement in a sql job that executes
as the admin. The insert is a simple one from one table to another.
Ultimately, I need to insert from a VIEW that is set up w/ a linked server
pulling data from FoxPro.
Here is the error message:
Executed as user: DOMAIN\admin. The operation could not be performed because
the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
[SQLSTATE 42000] (Error 7391) Driver not capable] [SQLSTATE 01000] (Error
7312) OLE DB error trace [OLE/DB Provider 'MSDASQL'
ITransactionJoin::JoinTransaction returned 0x8004d00a]. [SQLSTATE 01000]
(Error 7300). The step failed.
I made a change a few days ago to resolve a memory issue. The change was I
disabled Allow InProcess on the OLE DB for ODBC Drivers provider. Doesn't
seem like this error would be related and I haven't been able to prove
otherwise when enabling the option, for instance.
Any help is appreciated. Thank you, PhilNo resolution yet but I believe the problem is in a trigger on the table I a
m
inserting to. The trigger has sql code that uses views built on a linked
server getting data from foxpro.
"phil" wrote:
> I'm getting this error and I don't know where to begin. Our environment i
s
> SQL Server 2000 sp3a on Win Server 2003.
> I get the error when running an insert statement in a sql job that execute
s
> as the admin. The insert is a simple one from one table to another.
> Ultimately, I need to insert from a VIEW that is set up w/ a linked server
> pulling data from FoxPro.
> Here is the error message:
> Executed as user: DOMAIN\admin. The operation could not be performed becau
se
> the OLE DB provider 'MSDASQL' was unable to begin a distributed transactio
n.
> [SQLSTATE 42000] (Error 7391) Driver not capable] [SQLSTATE 01000] (Error
> 7312) OLE DB error trace [OLE/DB Provider 'MSDASQL'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]. [SQLSTATE 01000]
> (Error 7300). The step failed.
> I made a change a few days ago to resolve a memory issue. The change was
I
> disabled Allow InProcess on the OLE DB for ODBC Drivers provider. Doesn't
> seem like this error would be related and I haven't been able to prove
> otherwise when enabling the option, for instance.
> Any help is appreciated. Thank you, Phil
SQL Server 2000 sp3a on Win Server 2003.
I get the error when running an insert statement in a sql job that executes
as the admin. The insert is a simple one from one table to another.
Ultimately, I need to insert from a VIEW that is set up w/ a linked server
pulling data from FoxPro.
Here is the error message:
Executed as user: DOMAIN\admin. The operation could not be performed because
the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
[SQLSTATE 42000] (Error 7391) Driver not capable] [SQLSTATE 01000] (Error
7312) OLE DB error trace [OLE/DB Provider 'MSDASQL'
ITransactionJoin::JoinTransaction returned 0x8004d00a]. [SQLSTATE 01000]
(Error 7300). The step failed.
I made a change a few days ago to resolve a memory issue. The change was I
disabled Allow InProcess on the OLE DB for ODBC Drivers provider. Doesn't
seem like this error would be related and I haven't been able to prove
otherwise when enabling the option, for instance.
Any help is appreciated. Thank you, PhilNo resolution yet but I believe the problem is in a trigger on the table I a
m
inserting to. The trigger has sql code that uses views built on a linked
server getting data from foxpro.
"phil" wrote:
> I'm getting this error and I don't know where to begin. Our environment i
s
> SQL Server 2000 sp3a on Win Server 2003.
> I get the error when running an insert statement in a sql job that execute
s
> as the admin. The insert is a simple one from one table to another.
> Ultimately, I need to insert from a VIEW that is set up w/ a linked server
> pulling data from FoxPro.
> Here is the error message:
> Executed as user: DOMAIN\admin. The operation could not be performed becau
se
> the OLE DB provider 'MSDASQL' was unable to begin a distributed transactio
n.
> [SQLSTATE 42000] (Error 7391) Driver not capable] [SQLSTATE 01000] (Error
> 7312) OLE DB error trace [OLE/DB Provider 'MSDASQL'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]. [SQLSTATE 01000]
> (Error 7300). The step failed.
> I made a change a few days ago to resolve a memory issue. The change was
I
> disabled Allow InProcess on the OLE DB for ODBC Drivers provider. Doesn't
> seem like this error would be related and I haven't been able to prove
> otherwise when enabling the option, for instance.
> Any help is appreciated. Thank you, Phil
Subscribe to:
Posts (Atom)