Chris Weldon’s Blog

As I’m trying to finish up one of my consulting projects (and enhance other active projects), I decided to take a nice long hard look a PostgreSQL permissions - the correct way of doing it. PostgreSQL applies the commonly accepted principle of assigning permissions to resources. They’re moving away from the concept of users and groups and more to roles. As of PostgreSQL 8.3, the primary command to create new users OR groups is CREATE ROLE. The existing CREATE USER / CREATE GROUP commands still exist, but are becoming non-existent in hopes of replacing the user / group permissions model with a strictly role-based model.

In an attempt to move my projects towards this model, I ended up running into several problems.
Read more…

SQL postgresql, sql

I’ve been moving the databases at work from one Microsoft SQL 2005 server to another SQL 2005 Server. When the databases grant security to non-domain users, they have a tendency to not be attached / accessible on the new server. What you have to do is reattach / rejoin the orphaned security authorization to the credential that you reestablish on the new SQL Server. This is done with the following pl_Sql command:

sp_change_users_login 'Auto_Fix'
sp_change_users_login 'Update_One', 'old_username', 'new_username'

Auto_Fix can only be used if the old_username and the new_username are the same.

Uncategorized security, sql, stored procedures