Access a share on the network using different credentials

To stop Explorer raising errors when you try to access the network share or when you try to start Explorer using different credentials, you have to do some command link magic first.

Open a new command shell and run this command if the share is accessibly using only your admin credentials:

net use \SERVERShare * /USER:DOMadmin_user

Type the password at the prompt:

You should see output indicating success:

The command completed successfully.

Now when you try to access SERVERShare in Explorer, it should just work!

Effect of `SET NOCOUNT ON` on `@@ROWCOUNT`

From the @@ROWCOUNT documentation:

Statements such as USE, SET , DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT TRANSACTION reset the ROWCOUNT value to 0.

Which means that if you get into the good habit of beginning all your stored procedure bodies with SET NOCOUNT ON;, you’ll be in for a nasty surprise when you code a trigger with short-curcuit logic.

For example, in this audit trigger, the INSERT statement will never execute:

CREATE TRIGGER dbo.Wibble_InsertAuditRow
ON dbo.Wibble
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;

  IF @@ROWCOUNT = 0
  BEGIN
    RETURN;
  END;

  INSERT INTO Audit.WibbleHistory (
    Action,
    WibbleCode,
    WobbleSpec,
    GibletCount
  )
  SELECT
    'INSERT',
    WibbleCode,
    WobbleSpec,
    GibletCount
  FROM INSERTED
  UNION ALL
  SELECT
    'DELETE',
    WibbleCode,
    WobbleSpec,
    GibletCount
  FROM DELETED
END;

(Let’s assume that Audit.WibbleHistory has extra columns with default values that audit when and by whom each change is made.)

Because SET NOCOUNT ON sets @@ROWCOUNT to zero, the do-nothing code path will always execute.

To avoid this, make sure to check @@ROWCOUNT before setting NOCOUNT.

sp_change_users_login and ALTER USER

Crusty old SQL Server DBAs know all sorts of stored procedures for fixing up databases. A very useful one is sp_change_users_login, which you can use to map a database user to a server login after restoring a database from a backup.

You will need to do this when rebuilding a database server from scratch, because the backup does not store information about logins or any of the other server-level assets.

If you have to map the server login appuser to the SuperApp database user appuser, and you want to stay crusty, you can use

EXECUTE SuperApp.dbo.sp_change_users_login
  @Action = 'Auto_Fix',
  @UserNamePattern = N'appuser';

But you should prefer the new T-SQL language constructs, because the stored proc is going to be removed in a future version of SQL Server, and it is confisingly overloaded – it can change mapping or report on mapping depending on what value you pass to the @Action parameter.

The shiny new way top do the same is the ALTER USER statement:

USE DATABASE SuperApp;
ALTER USER appuser WITH LOGIN = appuser;

PostgreSQL Windows 7 Installation Instructions

Incomplete.

ADMIN:

Create a postgres user account. Prompt for a password.

net user postgres * /add

Download the PostreSQL 9.1.4 Windows 32-bit binaries from
http://get.enterprisedb.com/postgresql/postgresql-9.1.4-1-windows-binaries.zip.

Unzip to %PROGRAMFILES%PostgreSQL9.1.4.

Add bin directory (%PROGRAMFILES%PostgreSQL9.1.4bin) to the system path.

Create directory %PROGRAMFILES%PostgreSQL9.1.4data.

Grant read, execute, and write for data to postgres.

icacls data /grant postgres:(OI)(CI)RXW

POSTGRES:

Initialize a new PostgreSQL database cluster at %PROGRAMFILES%PostgreSQL9.1.4data. Call the superuser postgres. Prompt for a superuser password on initialization.

initdb --username postgres --pwprompt --pgdata "%PROGRAMFILES%PostgreSQL9.1.4data"

ADMIN:

Grant read, execute, and write for all files in data to postgres.

TODO

POSTGRES:

C:Program FilesPostgreSQL9.1.4>pg_ctl register -N postgres-9.1 -U postgres -D

“%PROGRAMFILES%Postgres9.1data”

Fails:

pg_ctl: could not open service manager

“I’m getting permissions errors when installing/running initdb”

The PostgreSQL wiki has a helpful section on Common Installation Errors:

I’m getting permissions errors when installing/running initdb

Make sure the PostgreSQL service account has permissions on the directories leading up to the one you have installed into. The installer will set permissions on the install directory but not on parent directories of it.

You may also see related errors show up as The database Cluster initialisation failed during the One Click installer. Check your install-postgresql log but it is usually related to permission errors. The following thread may help,

http://forums.enterprisedb.com/posts/list/2044.page#7503

Or alternatively, you can fix up the directory permissions and then manually restart the initcluster.vbs script like this for v9,

cscript //NoLogo "<install_path>/installer/server/initcluster.vbs" "postgres" "postgres" "<password>" "<install_path>" "<data_path>" 5432 "DEFAULT"

The recommended fix is basically ‘do the installer’s job yourself’. The install script, initcluster.vbs, is pretty broken. It tries to use the icacls utility to set the correct permissions, but uses the wrong syntax. On my machine, the script invokes the command:

icacls "C:Program FilesPostgreSQL9.1" /grant Sco:(RX)(NP)

The command fails with the following error:

Invalid parameter "Sco:(RX)(NP)"

From the icacls documentation:

Perm is a permission mask that can be specified in one of the following forms:

  • A sequence of simple rights:

    • F (full access)
    • M (modify access)
    • RX (read and execute access)
    • R (read-only access)
    • W (write-only access)
  • A comma-separated list in parenthesis of specific rights:

    • D (delete)
    • RC (read control)
    • WDAC (write DAC)
    • WO (write owner)
    • S (synchronize)
    • AS (access system security)
    • MA (maximum allowed)
    • GR (generic read)
    • GW (generic write)
    • GE (generic execute)
    • GA (generic all)
    • RD (read data/list directory)
    • WD (write data/add file)
    • AD (append data/add subdirectory)
    • REA (read extended attributes)
    • WEA (write extended attributes)
    • X (execute/traverse)
    • DC (delete child)
    • RA (read attributes)
    • WA (write attributes)

Inheritance rights may precede either Perm form, and they are applied only to directories:

  • (OI): object inherit
  • (CI): container inherit
  • (IO): inherit only
  • (NP): do not propagate inherit

Inheritance rights precede perms, and simple perms do not require parenthesis. So, the syntactically correct parameter is Sco:(NP)RX.

Almost makes me want to use SQL Server Express instead. But I’ll sleep on it now and persevere tomorrow.

PostgreSQL installs with errors, even as Administrator

When I first tried to install PostgreSQL, it failed. I assumed the problem was caused by not running the installer with administrative privileges.

I uninstalled PostgreSQL and tried again, this time running the installer as Administrator.

The installation has failed again, this time with a different set of errors. A dialog box appeared at the end of the installation:

Problem running post-install step. Installation may not complete correctly.
The database cluster initialization failed.

From the log file:

Error running cscript //NoLogo "C:Program FilesPostgreSQL9.1/installer/server/initcluster.vbs" "postgres" "postgres" "****" "C:Program FilesPostgreSQL9.1" "C:Program FilesPostgreSQL9.1data" 5432 "DEFAULT" : Program ended with an error exit code
Problem running post-install step. Installation may not complete correctly
 The database cluster initialisation failed.

The log provides more detail. This first error looks like it’s passing a bad parameter to icacls.

Ensuring we can read the path C:Program FilesPostgreSQL9.1 (using icacls) to Sco:
    Executing batch file 'rad86B3F.bat'...
    Invalid parameter "Sco:(RX)(NP)"

The second error looks like a permissions issue.

fixing permissions on existing directory C:/Program Files/PostgreSQL/9.1/data ... initdb: could not change permissions of directory "C:/Program Files/PostgreSQL/9.1/data": Permission denied

The first error could be as a result of the second. I’ll investigate the script initcluster.vbs to see if I can work out what’s going wrong.

Uninstall PostgreSQL cleanly

The Windows uninstaller of PostgreSQL 9.1 does not remove postgres service account or the server data directory, for which the default value is C:Program FilesPostgreSQL9.1data.

To completely remove all PostgreSQL artefacts, run the uninstaller, open an elevated command prompt, and issue the following commands:

net user /delete postgres
rmdir /s /q "C:Program FilesPostgreSQL"
del %temp%install-postgresql.log
del %temp%uninstall-postgresql.log
del %temp%bitrock_installer*.log

This performs the following cleanup tasks:

  • Remove the postgres service account.
  • Remove Postgres’s program files directory, which implicitly removes the data directory as well.
  • Delete all log files created during the installation and uninstallation processes. The last command uses a glob because the BitRock installer generates a new file every time it is run.