DSO in SQL2005: runs

Showing posts with label runs. Show all posts

Tuesday, March 27, 2012

Dropping and Recreating Tables

We have a job that runs every 30 minutes dropping and recreating a table with
updated information. Besides the fact that I am trying to get rid of this
needless process, the question I have is:
When this job is running and attempting to drop and recreate the table will
it not be able to complete if someone has a connection to the database and/or
is running a report off the table that SQL Server is attempting to drop and
recreate? If there is a lock on this table from someone using it, is there a
way I can stop this from happening?Connections to the database itself will not matter however all locks
involving the table would need to be released before the DROP command
executes. In the event that someone is using the table, the drop command
will wait until the locks are released or your connection times out (which
ever comes first).
--
--Brian
(Please reply to the newsgroups only.)
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
> We have a job that runs every 30 minutes dropping and recreating a table
> with
> updated information. Besides the fact that I am trying to get rid of this
> needless process, the question I have is:
> When this job is running and attempting to drop and recreate the table
> will
> it not be able to complete if someone has a connection to the database
> and/or
> is running a report off the table that SQL Server is attempting to drop
> and
> recreate? If there is a lock on this table from someone using it, is there
> a
> way I can stop this from happening?|||Thanks for the reply. That should help a lot. Another question to your reply
though. If the job is waiting for the locks to be released, will this cause
the database to become unresponsive through Enterprise Manager, and will this
slow database access (as a whole) through the website?
"Brian Lawton" wrote:
> Connections to the database itself will not matter however all locks
> involving the table would need to be released before the DROP command
> executes. In the event that someone is using the table, the drop command
> will wait until the locks are released or your connection times out (which
> ever comes first).
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
> > We have a job that runs every 30 minutes dropping and recreating a table
> > with
> > updated information. Besides the fact that I am trying to get rid of this
> > needless process, the question I have is:
> >
> > When this job is running and attempting to drop and recreate the table
> > will
> > it not be able to complete if someone has a connection to the database
> > and/or
> > is running a report off the table that SQL Server is attempting to drop
> > and
> > recreate? If there is a lock on this table from someone using it, is there
> > a
> > way I can stop this from happening?
>
>|||While the DROP command is waiting, unless its connection is holding locks on
other resources, then no, it will not impact any performance of EM until it
actually executes. While executing, it may impact performance depending on
the table size involved and the time it takes to complete its work. This is
especially true if the web site is waiting for the "refreshed" table to be
repopulated.
All that said, if you are issuing the DROP command through EM, then yes, EM
will become "unresponsive" while it waits for the command to complete.
Unfortunately EM operates synchronously so any operation you perform using
EM may cause it to pause while the operation completes.
--
--Brian
(Please reply to the newsgroups only.)
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:2F58BF53-683A-4E5B-A664-0F418DD019EF@.microsoft.com...
> Thanks for the reply. That should help a lot. Another question to your
> reply
> though. If the job is waiting for the locks to be released, will this
> cause
> the database to become unresponsive through Enterprise Manager, and will
> this
> slow database access (as a whole) through the website?
> "Brian Lawton" wrote:
>> Connections to the database itself will not matter however all locks
>> involving the table would need to be released before the DROP command
>> executes. In the event that someone is using the table, the drop command
>> will wait until the locks are released or your connection times out
>> (which
>> ever comes first).
>> --
>> --Brian
>> (Please reply to the newsgroups only.)
>>
>> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
>> news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
>> > We have a job that runs every 30 minutes dropping and recreating a
>> > table
>> > with
>> > updated information. Besides the fact that I am trying to get rid of
>> > this
>> > needless process, the question I have is:
>> >
>> > When this job is running and attempting to drop and recreate the table
>> > will
>> > it not be able to complete if someone has a connection to the database
>> > and/or
>> > is running a report off the table that SQL Server is attempting to drop
>> > and
>> > recreate? If there is a lock on this table from someone using it, is
>> > there
>> > a
>> > way I can stop this from happening?
>>

Wednesday, March 21, 2012

Drop table statement didn't actually drop table...

I have a scheduled job that runs every minute to monitor for
long-running jobs in production databases. This job has been running
without issue for at least 8 months now. I have not done ANY work on
this server in at least 2 months and no one has rights to do anything
within the SQL Server on this box except me. A couple of days ago, I
started getting job failure messages sporradically and the reason was
that '##jobs already exists in the database'. This is a global temp
table that gets created, populated, and dropped in the stored procedure
that's run in my job.
I ran a trace to see what's going on and why the job thinks the temp
table still exists. I can see where the DROP TABLE statement for the
temp table executes and completes on a job run that succeeds, but if
the next job run fails on the statement that creates the temp table, I
can do a select for that table and it does exist and is still populated
with the records from the previous run. I also ran the trace with
errors/exceptions and none were picked up.
Has anyone ever seen where a DROP TABLE statement is issued and
completes, but the table doesn't actually drop? Below you will find
the stored procedure, the results of the trace (where the first job run
wraps up with the final statements and the next job run tries to
execute and fails), and the select from the temp table after the DROP
TABLE statement is executed. Sorry for the length.
Any ideas would be appreciated!
STORED PROCEDURE
--
CREATE PROCEDURE [dbo].[LongRunningJobs]
@.emailaddress varchar(100)
AS
-- Description: This stored procedure will detect long running jobs.
-- A long running job is defined as a job that has
-- been running longer than what is specified as the time
-- limit for the job in the LongRunningJobs_JobTimeLimits
-- table. If it detects a long running job an email is sent.
-- DECLARE variables
SET NOCOUNT ON
DECLARE @.jobcount int
DECLARE @.jobid uniqueidentifier
DECLARE @.jobname sysname
DECLARE @.spid smallint
DECLARE @.logintime datetime
DECLARE @.duration int
-- CREATE TABLE to hold job information
CREATE TABLE #enum_job (
Job_ID uniqueidentifier,
Last_Run_Date int,
Last_Run_Time int,
Next_Run_Date int,
Next_Run_Time int,
Next_Run_Schedule_ID int,
Requested_To_Run int,
Request_Source int,
Request_Source_ID varchar(100),
Running int,
Current_Step int,
Current_Retry_Attempt int,
State int
)
-- CREATE TABLE to hold job_id and the job_id in hex character format
CREATE TABLE ##jobs (
job_id uniqueidentifier ,
job_id_char varchar(100)
)
-- Get a list of jobs
INSERT INTO #enum_job
EXEC master.dbo.xp_sqlagent_enum_jobs 1, '' -- doesn't seem to
matter what you put here
-- calculate the #jobs TABLE with job_id's
-- and their hex character representation
INSERT INTO ##jobs
SELECT job_id, dbo.fn_hex_to_char(job_id,16) FROM #enum_job
-- get a COUNT of long running jobs
SELECT @.jobcount = COUNT(*)
FROM master.dbo.sysprocesses a
join ##jobs b
ON SUBSTRING(a.program_name,32,32)= b.job_id_char
join msdb.dbo.sysjobs c
ON b.job_id = c.job_id
join dbo.LongRunningJobs_JobTimeLimits d
ON c.job_id = d.job_id
WHERE login_time < DATEADD(mi,-(d.minutes),GETDATE()) -- check for jobs
that have been running longer than 1 minute.
-- record long running jobs in LongRunningJobs_Jobs
IF @.jobcount > 0
BEGIN
UPDATE LongRunningJobs_Jobs
SET still_running = 0
DECLARE jobs_cursor CURSOR
FOR
SELECT SUBSTRING(c.name,1,78), c.job_id, a.spid, a.login_time
FROM master.dbo.sysprocesses a
join ##jobs b
ON SUBSTRING(a.program_name,32,32)= b.job_id_char
join msdb.dbo.sysjobs c
ON b.job_id = c.job_id
join dbo.LongRunningJobs_JobTimeLimits d
ON c.job_id = d.job_id
WHERE login_time < DATEADD(mi,-(d.minutes),GETDATE())
OPEN jobs_cursor
FETCH NEXT FROM jobs_cursor INTO @.jobname, @.jobid, @.spid, @.logintime
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF NOT EXISTS (SELECT job_id, login_time FROM
dbo.LongRunningJobs_Jobs
WHERE job_id = @.jobid AND login_time = @.logintime)
BEGIN
SET @.duration = (SELECT DATEDIFF(mi, @.logintime, getdate()))
EXEC LongRunningJobs_Notification @.jobid, @.logintime, @.duration,
@.emailaddress
INSERT INTO dbo.LongRunningJobs_Jobs (job_id, job_name, spid,
login_time, still_running)
VALUES (@.jobid, @.jobname, @.spid, @.logintime, 1)
FETCH NEXT FROM jobs_cursor INTO @.jobname, @.jobid, @.spid, @.logintime
END
ELSE
UPDATE LongRunningJobs_Jobs
SET still_running = 1
WHERE job_id = @.jobid AND login_time = @.logintime
FETCH NEXT FROM jobs_cursor INTO @.jobname, @.jobid, @.spid, @.logintime
END
CLOSE jobs_cursor
DEALLOCATE jobs_cursor
END
ELSE
UPDATE LongRunningJobs_Jobs
SET still_running = 0
DELETE FROM LongRunningJobs_Jobs
WHERE still_running = 0
DROP TABLE #enum_job
DROP TABLE ##jobs
GO
TRACE RESULTS
--
EventClass TextData
-- --
SP:StmtStarting -- LongRunningJobs
UPDATE LongRunningJobs_Jobs
SET still_running = 0
SP:StmtCompleted -- LongRunningJobs
UPDATE LongRunningJobs_Jobs
SET still_running = 0
SP:StmtStarting -- LongRunningJobs
DELETE FROM LongRunningJobs_Jobs
WHERE still_running = 0
SP:StmtCompleted -- LongRunningJobs
DELETE FROM LongRunningJobs_Jobs
WHERE still_running = 0
SP:StmtStarting -- LongRunningJobs
DROP TABLE #enum_job
SP:StmtCompleted -- LongRunningJobs
DROP TABLE #enum_job
SP:StmtStarting -- LongRunningJobs
DROP TABLE ##jobs
SP:StmtCompleted -- LongRunningJobs
DROP TABLE ##jobs
SP:Completed EXEC ISS_DBAData_Production.dbo.LongRunningJobs
'rfsupport@.kroger.com'
SQL:StmtCompleted EXEC ISS_DBAData_Production.dbo.LongRunningJobs
'rfsupport@.kroger.com'
SQL:BatchCompleted EXEC ISS_DBAData_Production.dbo.LongRunningJobs
'rfsupport@.kroger.com'
SQL:BatchStarting EXEC ISS_DBAData_Production.dbo.LongRunningJobs
'rfsupport@.kroger.com'
SQL:StmtStarting EXEC ISS_DBAData_Production.dbo.LongRunningJobs
'rfsupport@.kroger.com'
SP:Starting EXEC ISS_DBAData_Production.dbo.LongRunningJobs
'rfsupport@.kroger.com'
SP:StmtStarting -- LongRunningJobs
SET NOCOUNT ON
SP:StmtCompleted -- LongRunningJobs
SET NOCOUNT ON
SP:StmtStarting -- LongRunningJobs
CREATE TABLE #enum_job (
Job_ID uniqueidentifier,
Last_Run_Date int,
Last_Run_Time int,
Next_Run_Date int,
Next_Run_Time int,
Next_Run_Schedule_ID int,
Requested_To_Run int,
Request_Source int,
Request_Source_ID varch
SP:StmtCompleted -- LongRunningJobs
CREATE TABLE #enum_job (
Job_ID uniqueidentifier,
Last_Run_Date int,
Last_Run_Time int,
Next_Run_Date int,
Next_Run_Time int,
Next_Run_Schedule_ID int,
Requested_To_Run int,
Request_Source int,
Request_Source_ID varch
SP:StmtStarting -- LongRunningJobs
CREATE TABLE ##jobs (
job_id uniqueidentifier ,
job_id_char varchar(100)
)
-- Get a list of jobs
SQL:BatchCompleted EXEC ISS_DBAData_Production.dbo.LongRunningJobs
'rfsupport@.kroger.com'
SELECT OF ##jobs AFTER DROP STATEMENT HAS BEEN EXECUTED SHOWS TABLE
STILL EXISTS
---
job_id job_id_char
--
----
0887FA02-2785-415B-A1E7-0F70EA9B2BF9 02FA870885275B41A1E70F70EA9B2BF9
A22E5C05-9331-4D13-A2E0-C4470EF51BEE 055C2EA23193134DA2E0C4470EF51BEE
B45F740A-3847-42E4-B5F6-0A7AC400E5B8 0A745FB44738E442B5F60A7AC400E5B8
7EDEBA0D-730C-4C57-BD79-4ABAF8D2FB8F 0DBADE7E0C73574CBD794ABAF8D2FB8F
6152F60D-7CA7-4ADD-A117-43D1E6A9E07E 0DF65261A77CDD4AA11743D1E6A9E07E
F7810610-F203-4C52-BE0C-BBF9CF68C887 100681F703F2524CBE0CBBF9CF68C887
F482D610-0171-4E1F-B43F-1266AE7506A2 10D682F471011F4EB43F1266AE7506A2
9FB2FA11-B69F-41DF-AD25-49FF5C605F37 11FAB29F9FB6DF41AD2549FF5C605F37
6CBB1318-95D3-4B03-B5B5-E8C0B526087C 1813BB6CD395034BB5B5E8C0B526087CForgot to mention:
SQL Server 2000 Enterprise Edition with sp4.
This is an active/active cluster.
The job is set up on both nodes and is only failing on the first node.|||Apan,
Why not try making the table a local temporary table instead of global
temporary table?...proc won't see it as an existing table...since you're
dropping it at the end of the proc anyway.
HTH
Jerry
"apan" <anapatterson@.yahoo.com> wrote in message
news:1148661240.078643.204250@.y43g2000cwc.googlegroups.com...
> Forgot to mention:
> SQL Server 2000 Enterprise Edition with sp4.
> This is an active/active cluster.
> The job is set up on both nodes and is only failing on the first node.
>|||I had originally set it up as a global temp table because there were
going to be other sprocs querying it. I've since changed this (nothing
else in the database calls on this temp table), so I probably could
change it to a local temp table.
However, that really doesn't answer my question as to why it's
occasionally not being dropped even though the DROP TABLE command
executes. I'm not sure why it would matter if it's local or global if
the DROP is called.|||FYI - I've gone ahead and changed the global to a local to see if this
fixes the problem since I don't really need a global. But I'd still
like to see if anyone's got any ideas as to my previous question...
BTW, thanks for your input Jerry...forgot to say that before.|||It wouldn't...any blocking occuring that could prevent this? My solution
was an imediate "get you past this" solution.
HTH
Jerry
"apan" <anapatterson@.yahoo.com> wrote in message
news:1148663035.537739.42460@.y43g2000cwc.googlegroups.com...
>I had originally set it up as a global temp table because there were
> going to be other sprocs querying it. I've since changed this (nothing
> else in the database calls on this temp table), so I probably could
> change it to a local temp table.
> However, that really doesn't answer my question as to why it's
> occasionally not being dropped even though the DROP TABLE command
> executes. I'm not sure why it would matter if it's local or global if
> the DROP is called.
>|||Nope, no blocking. I also traced locks and errors/exceptions in my
profile trace when I ran it and none showed up. Also checked the
Locks/Process ID in Enterprise Manager for blocking when I could see
that the temp table hadn't been dropped and didn't see any blocking
there.|||Hmmm...if you query for the ## table in TEMPDB does it still exist?
"apan" <anapatterson@.yahoo.com> wrote in message
news:1148664132.029575.250840@.38g2000cwa.googlegroups.com...
> Nope, no blocking. I also traced locks and errors/exceptions in my
> profile trace when I ran it and none showed up. Also checked the
> Locks/Process ID in Enterprise Manager for blocking when I could see
> that the temp table hadn't been dropped and didn't see any blocking
> there.
>|||When I queried the global table (see last 20 or so lines of original
post for query details), I believe I was just doing it from one of the
user db's and it found it and brought back data. I don't think it
would matter if I queried for it from TEMPDB since it was a global
table...or would it?|||The global temporary table IS stored in the TEMPDB database. But if you're
getting records back the it does exist.
"apan" <anapatterson@.yahoo.com> wrote in message
news:1148666005.668894.271130@.y43g2000cwc.googlegroups.com...
> When I queried the global table (see last 20 or so lines of original
> post for query details), I believe I was just doing it from one of the
> user db's and it found it and brought back data. I don't think it
> would matter if I queried for it from TEMPDB since it was a global
> table...or would it?
>

Drop table statement didn't actually drop table...

Drop Table Gets skipped in a query batch

I have a situation in SQL Svr 2k that I can't explain. I have a query batch that runs in a SQL step in a DTS package. All steps execute except the Drop Table statements. Here is a section of the code;

************************************************** ****
INSERT INTO Oral_VitDhistory ( MeNumber, DrugType, Territory, Quantity, SalesAmt, DataType, RXDate )
SELECT tbl_Oral_VitDHistory.MENumber, tbl_Oral_VitDHistory.DrugType, tbl_Oral_VitDHistory.Territory, tbl_Oral_VitDHistory.Quantity, tbl_Oral_VitDHistory.SalesAmt, tbl_Oral_VitDHistory.DataType, tbl_Oral_VitDHistory.RXDate
FROM Data_Warehouse..tbl_Oral_VitDHistory WHERE tbl_Oral_VitDHistory.Territory Is Not Null

drop table Data_Warehouse..NDC24month_Cost
drop table Data_Warehouse..NDC24month_Count

/* Process New Rx */

select MeNumber, zip, RXTypecode, DrugTYpe, NRX1 into NDC24month_Cost from Data_Warehouse..NDC24month where RxTypecode = '$ B'

Insert into tbl_Oral_VitDHistory(MeNumber, DrugType, Quantity, SalesAmt) select a.MeNumber,
a.DrugType, a.NRX1 as Quantity, b.NRX1 as SalesAmt from Data_Warehouse..NDC24month_Count as a, NDC24month_Cost
as b where a.Menumber = b.MeNumber and a.DrugType = b.DrugType
************************************************** *******

The drop table statements appear to get skipped and the code fails because the column names should have been changed as the dropped tables should have been recreated with a different column name.

This used to work fine. Then suddenly it stopped working. No changes to the SQL installation from when it worked to when it stopped. The batch also fails in the QA if cut and pasted and ran from there.

I ran accross this once before in a Stored Proc with the Drop table statement and had to put the statement in a different procedure.

Anybody ever run accross this? I have searched the MS KB to no avail.

Thanks in advance to the genius who can help a guy out!Howdy,

I have come across this before - usually the best way to get around it is to break the code into separate sprocs & then call each in turn from a main sproc. Even using BEGIN TRAN...COMMIT TRAN wont help....it seems to get a bee in its bonnet and thats it......

Also, if its in DTS package etc , break up the code into logical chunks with end of batch "GO" commands. This will mean separate sprocs if you run it in one sproc currently.

Cheers,

SG.|||Thanks for your help!

Thats about where I thought I would have to go with it.
You confirmed my suspicions!

Have a great one!

Originally posted by sqlguy7777
Howdy,

I have come across this before - usually the best way to get around it is to break the code into separate sprocs & then call each in turn from a main sproc. Even using BEGIN TRAN...COMMIT TRAN wont help....it seems to get a bee in its bonnet and thats it......

Also, if its in DTS package etc , break up the code into logical chunks with end of batch "GO" commands. This will mean separate sprocs if you run it in one sproc currently.

Cheers,

SG.

drop table

I upgraded my system from Sql2000 to sql2005.

And now i have a problem.

I have job wich runs every minute and perform the following

select * into tabler_1 from .....

begin tran

if exists(select * from sysobjects where xtype ='u' and name = 'tabler')

drop table tabler

exec sp_rename tabler_1, tabler

commit

And on client side i have IIS which runs asp pages which connect to database with

ado connction using ODBC and runs procedures wich performs select * from tabler

and there is a problem they some times failes with error tabler does not exists.

It worked fine in sql2000

all connections opened with default setting.

(transaction isolation level read commited)

What abou this here:

if not exists

(

SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'tabler_1'

AND TABLE_TYPE = 'BASE_TABLE'

)

select * into tabler_1 from SomeTable

if exists

(

SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'tabler'

AND TABLE_TYPE = 'BASE_TABLE'

)

BEGIN

DROP TABLE tabler

EXEC sp_rename tabler_1, tabler

END

Make sure that you check the existence of the table before doing a SELECT INTO.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

It is not a problem.

it make drop very nice.

the problem is on the client which runs store procedure like this

create procedure test_sp

select * from tabler

create procedure test_sp_d

exec ('select * from tabler')

when i droping table i do it in transaction so schema is locked

the procedures have to wait for commit and then select

it worked fine sql2000

sql

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

Tuesday, March 27, 2012

Dropping and Recreating Tables

Dropping and Recreating Tables

Dropping and Recreating Tables

Wednesday, March 21, 2012

Drop table statement didn't actually drop table...

Drop table statement didn't actually drop table...

Drop Table Gets skipped in a query batch

drop table

Monday, March 19, 2012

drop login Stored Procedure

drop login Stored Procedure

drop login Stored Procedure

DSO in SQL2005

Blog Archive

About Me