DBA: DC City Stories: SQL Server 2000 Disable User Account

SQL Server 2000 doesn't have the ALTER LOGIN statement. So to be able to disable the login you'll have to call the sp_denylogin procedure instead:

EXEC sp_denylogin 'exampleuser'

or

EXEC sp_revokelogin 'exampleuser'

To give them back access again you should use:

EXEC sp_grantlogin 'exampleuser'


Note: sp_denylogin, sp_revokelogin and sp_grantlogin only works on Windows accounts and groups.

To be able to deny pure SQL Server logins, it seems like the only option is to remove that login completely with

EXEC sp_droplogin 'exampleuser'


but to enable it again, it needs to be re-created with:

EXEC sp_addlogin 'exampleuser', 'examplepassword'


or just remove that logins access to the current database with


EXEC sp_revokedbaccess 'exampleuser'

DBA: DC City Stories: Datapump

Oracle always has a way with error message. Why can't they make it clear and precise?

I was doing a database export using datapump for a client, and I passed in PARFILE. The following error was spat out:

ksunsodb01:oracle\(KTECH\)@ expdp parfile=expdp.par

Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 07 July, 2011 11:09:21

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-29283: invalid file operation

ORA-06512: at "SYS.UTL_FILE", line 488

ORA-29283: invalid file operation

Not very clear huh? Well, what Oracle was complaining about was that it was missing the Datapump directory on a OS level. Therefore, check which directory your PARFILE is using: 

ksunsodb01:oracle\(KTECH\)@  cat parfile_expdp_WMDATA.par

USERID="/ as sysdba"

dumpfile=DATA_PUMP_DIR:expdp_KTECH_WMDATA_20110707.dmp

logfile=DATA_PUMP_DIR:logexpdp_KTECH_WMDATA_20110707.log

schemas=WMDATA

flashback_time="to_timestamp(to_char(sysdate, 'dd/mon/yyyy hh24:mi:ss'), 'dd/mon/yyyy hh24:mi:ss')"

In our case, the directory is "DATA_PUMP_DIR". Find out where this directory is mapped to:

ksunsodb01:oracle\(KTECH\)@  sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 7 11:14:07 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> conn / as sysdba

Connected.

SQL> select directory_path, directory_name from dba_directories;

DIRECTORY_NAME                                                        DIRECTORY_PATH                     

------------------------------------------------------------
/u06/flash2/exports/OTECH/         DATA_PUMP_DIR

And check that the directory actually exists. In our case it doesn't, so modify it to a directory that exists:

SQL> create or replace directory DATA_PUMP_DIR as '/u06/flash1/export/KTECH/';

Directory created.

SQL> exit

Now, we're ready to run again. :)