DSO in SQL2005: invalid

Showing posts with label invalid. Show all posts

Sunday, March 25, 2012

Dropped column being "dropped" before drop ??

(SQL Server 2000 SP3, Developer Edition, Windows XP Professional)
I am getting an error "Invalid column name 'sys_login_name'." when running
the following query. The query is a minor conversion of a table between
versions. One column (userid_fk) had been added in a previous batch. In
this batch several columns are being dropped IF they exist. In the first
column 'sys_login_name', before it is dropped, data is pulled in from
another table (sy_user) before the sys_user.sys_login_name is dropped. I've
used col_length() as a quick way to detect if a column exists (returns null
if it doesn't exist). In the code below, note the section where "if
col_length('sys_user', 'sys_login_name') is not null" which means it only
gets executed when sys_login_name DOES exist. The UPDATE statement in that
IF block pulls data into sys_user from sy_user based upon the sys_login_name
field. The next statement then DROPS the sys_login_name field. In Query
Analyzer, I'm getting an "Invalid column name 'sys_login_name'" error that
points back to the UPDATE statement, but the rest of the batch is executing.
The output from Query Analyzer is:
=====OUTPUT
START===================================
====================================
====
doing v2->v3 on sys_user
updating sys_user
dropping sys_login_name
dropping other columns
Server: Msg 207, Level 16, State 3, Line 14
Invalid column name 'sys_login_name'.
=====OUTPUT
END=====================================
====================================
==
Oddly enough, the error is after the PRINT statement's output, but I've seen
that asynchronous-ness (?) of PRINT and error output enough before to not be
alarmed.
Here's the batch:
=====BATCH
START===================================
====================================
====
-- v2->v3: Check if sy integration changes need to be done STEP 2: convert
and drop fields
if dbo.fn_sy_get_table_version(N'sys_user') = 2
begin
print 'doing v2->v3 on sys_user'
-- if sys_login_name exists, pull data from sy_user for conversion
-- and drop sys_login_name
if col_length('sys_user', 'sys_login_name') is not null
begin
-- fill in userid_fk from sy_user.userid_pk via login name
-- and set password to 'test' for all accounts
-- before dropping login name; entry may not exist in sy_user
print 'updating sys_user'
update sys_user
set userid_fk = isnull(SY.userid_pk, 0),
sys_password = '098f6bcd4621d373cade4e832627b4f6'
from sys_user SYS left join sy_user SY on SYS.sys_login_name =
SY.login
print 'dropping sys_login_name'
alter table sys_user drop column sys_login_name
end
print 'dropping other columns'
-- drop sys_user_first if it exists
if col_length('sys_user', 'sys_user_first') is not null
alter table sys_user drop column sys_user_first
-- drop sys_user_last if it exists
if col_length('sys_user', 'sys_user_last') is not null
alter table sys_user drop column sys_user_last
-- drop timestamp if it exists
if col_length('sys_user', 'timestamp') is not null
alter table sys_user drop column [timestamp]
exec sp_sy_addextprops N'PPD_Version', 3, N'USER', N'dbo', N'TABLE',
N'sys_user'
end
go
=====BATCH
END=====================================
===================================
Here's a sample I did to test to see if ALTER TABLE DROP COLUMN somehow gets
executed before the UPDATE, but it doesn't:
=====SAMPLE
START===================================
====================================
=
create table testdrop
(
ident int identity(100,1) not null primary key,
col1 int null,
col2 int null
)
go
insert testdrop (col1, col2) values (1,2)
update testdrop set col2 = 22 where col1=1
select * from testdrop
alter table testdrop drop column col2
select * from testdrop
go
drop table testdrop
go
=====SAMPLE
END=====================================
===================================
Thanks for any help!
Mike JansenOK, I was able to reproduce the problem by enhancing my sample:
========= BEGIN SAMPLE ================
create table testdrop
(
ident int identity(100,1) not null primary key,
col1 int null,
col2 int null
)
go
create table testdrop2
(
pk int identity(100,1) not null primary key,
col1 int null
)
go
insert testdrop2 (col1) values (1)
insert testdrop2 (col1) values (2)
insert testdrop (col1, col2) values (1,0)
insert testdrop (col1, col2) values (2,0)
insert testdrop (col1, col2) values (3,0)
select * from testdrop
update testdrop
set col2 = T2.pk
from testdrop T1 left join testdrop2 T2 on T1.col1=T2.col1
select * from testdrop
--go
alter table testdrop drop column col2
select * from testdrop
go
drop table testdrop
drop table testdrop2
go
========= END SAMPLE ====================
Note that if you uncomment the one GO statement, it works. If the ALTER
TABLE DROP COLUMN is in the same batch as the UPDATE with the JOIN in the
FROM clause, it has the error.
Thanks,
Mike|||Does anyone have any idea about the following problem with the UPDATE
statement not working (get "Invalid Column" error) when you use a column in
the UPDATE's FROM clause (in a JOIN) and then drop that column via ALTER
TABLE in the same batch?
I can work around the problem, but I'd like to know if this is a SQL bug or
if I am ignorant of something fundamental in SQL Server (working with the
guys I work with, I had to qualify what I might be ignorant about or they
might pipe in all too quickly to confirm that I'm just ignorant <g> )
Thanks,
Mike
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:ek5KSIaVFHA.1508@.tk2msftngp13.phx.gbl...
> OK, I was able to reproduce the problem by enhancing my sample:
> ========= BEGIN SAMPLE ================
> create table testdrop
> (
> ident int identity(100,1) not null primary key,
> col1 int null,
> col2 int null
> )
> go
> create table testdrop2
> (
> pk int identity(100,1) not null primary key,
> col1 int null
> )
> go
> insert testdrop2 (col1) values (1)
> insert testdrop2 (col1) values (2)
> insert testdrop (col1, col2) values (1,0)
> insert testdrop (col1, col2) values (2,0)
> insert testdrop (col1, col2) values (3,0)
> select * from testdrop
> update testdrop
> set col2 = T2.pk
> from testdrop T1 left join testdrop2 T2 on T1.col1=T2.col1
> select * from testdrop
> --go
> alter table testdrop drop column col2
> select * from testdrop
> go
> drop table testdrop
> drop table testdrop2
> go
> ========= END SAMPLE ====================
> Note that if you uncomment the one GO statement, it works. If the ALTER
> TABLE DROP COLUMN is in the same batch as the UPDATE with the JOIN in the
> FROM clause, it has the error.
> Thanks,
> Mike
>|||Dropping the column forces a recompile of the entire batch. That's why
you get an error. It's the expected behaviour.
For this and other reasons try to keep DDL and DML code entirely
separate. Put your ALTER statements in a separate batch.
David Portas
SQL Server MVP
--|||1. If it's recompiling the batch because of the DDL, why does my "simple
sample" work where I have an UPDATE with no FROM clause but I SET the column
and then drop it in the next line with an ALTER TABLE? If it were
recompiling the batch, I'd think that it would fail on that as well.
Here's a re-post of my simple sample that works:
==== BEGIN SAMPLE ==============
create table testdrop
(
ident int identity(100,1) not null primary key,
col1 int null,
col2 int null
)
go
insert testdrop (col1, col2) values (1,2)
update testdrop set col2 = 22 where col1=1
select * from testdrop
alter table testdrop drop column col2
select * from testdrop
go
drop table testdrop
go
==== END SAMPLE ==============
2. What are the other reasons for putting DDL and DML in separate batches
(besides the re-compile issue)?
Thanks for your help,
Mike
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1115815055.958304.192040@.g47g2000cwa.googlegroups.com...
> Dropping the column forces a recompile of the entire batch. That's why
> you get an error. It's the expected behaviour.
> For this and other reasons try to keep DDL and DML code entirely
> separate. Put your ALTER statements in a separate batch.
> --
> David Portas
> SQL Server MVP
> --
>|||I did a little research and found the answer to question #2 (What are the
other reasons for putting DDL and DML in separate batches - besides the
re-compile issue): It's related to the re-compile issue: performance. The
recompilation obviously causes performance issues. Since the compilation of
the batches is probably 1% or less of the time in the scenario I'm talking
about and it's a once-in-a-while script, that isn't really a significant
factor. I did end up changing my script though to put the DDL and DML in
separate batches since I'm still getting the "invalid column" error -- which
probably is related to the re-compiling (perhaps in the simple example
something is optimized in such a way that the batch didn't need to be
re-compiled ')
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:O1nV6niVFHA.2420@.TK2MSFTNGP12.phx.gbl...
> 1. If it's recompiling the batch because of the DDL, why does my "simple
> sample" work where I have an UPDATE with no FROM clause but I SET the
column
> and then drop it in the next line with an ALTER TABLE? If it were
> recompiling the batch, I'd think that it would fail on that as well.
> Here's a re-post of my simple sample that works:
> ==== BEGIN SAMPLE ==============
> create table testdrop
> (
> ident int identity(100,1) not null primary key,
> col1 int null,
> col2 int null
> )
> go
> insert testdrop (col1, col2) values (1,2)
> update testdrop set col2 = 22 where col1=1
> select * from testdrop
> alter table testdrop drop column col2
> select * from testdrop
> go
> drop table testdrop
> go
> ==== END SAMPLE ==============
> 2. What are the other reasons for putting DDL and DML in separate batches
> (besides the re-compile issue)?
>
> Thanks for your help,
> Mike
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1115815055.958304.192040@.g47g2000cwa.googlegroups.com...
>

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
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
>

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

Sunday, March 25, 2012

Dropped column being "dropped" before drop ??

Monday, March 19, 2012

drop login Stored Procedure

drop login Stored Procedure

drop login Stored Procedure

DSO in SQL2005

Blog Archive

About Me