If you have a list of known usernames, you can store them in a table somewhere:
CREATE TABLE dbo.WebDevelopers(name SYSNAME PRIMARY KEY);
INSERT dbo.WebDevelopers(name)
SELECT N'YourDomain\User1'
UNION ALL
SELECT N'YourDomain\User2';
Now you can create a variety of scripts to ensure that:
- they exist as a login at the server level
for each relevant database:
- they exist as a user
- they are in the
db_datareader
role
- they have
SELECT
permission on the dbo
schema
Here is a start to solve 1. (I'll come back and address the others as I have time later today):
DECLARE @sql NVARCHAR(MAX), @cr NCHAR(2) = CHAR(13) + CHAR(10);
SELECT @sql = N'';
SELECT @sql = @sql + @cr + 'CREATE LOGIN '
+ QUOTENAME(d.name) + ' FROM WINDOWS;'
FROM dbo.WebDevelopers AS d
LEFT OUTER JOIN sys.server_principals AS p
ON d.name = p.name
WHERE p.name IS NULL;
PRINT @sql;
-- EXEC sp_executesql @sql;
For the rest, this makes some basic assumptions: you always elevate privileges using database-level roles, rather than explicit GRANT/DENY, server role membership or AD group membership. Like the table of developers, you also want to have somewhere a list of the databases you want to affect:
CREATE TABLE dbo.DeveloperDatabases(name SYSNAME PRIMARY KEY);
INSERT dbo.DeveloperDatabases(name)
SELECT N'some_database'
-- UNION ALL N'...';
Now you can build a script that will drop the user from each database, create the entire list from scratch, and add each user only to the db_datareader role. You may want to customize the script if you want to grant explicit rights on a certain schema or set of objects.
DECLARE @sql NVARCHAR(MAX), @cr NCHAR(2) = CHAR(13) + CHAR(10);
SELECT @sql = N'SELECT @sql2 = N'''';';
SELECT @sql = @sql + @cr + N'SELECT @sql2 = @sql2 + ''
USE ' + QUOTENAME(name) + ';
DROP USER '' + QUOTENAME(d.name) + '';''
FROM dbo.WebDevelopers AS d
INNER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
ON d.name = p.name;'
FROM dbo.DeveloperDatabases;
SELECT @sql = @sql + @cr + N'SELECT @sql2 = @sql2 + ''
USE ' + QUOTENAME(name) + ';
CREATE USER '' + QUOTENAME(d.name) + '' FROM LOGIN '' + QUOTENAME(d.name) + '';
EXEC sp_addrolemember ''''db_datareader'''', '''''' + d.name + '''''';''
FROM dbo.WebDevelopers AS d
LEFT OUTER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
ON d.name = p.name
WHERE p.name IS NULL;'
FROM dbo.DeveloperDatabases;
DECLARE @sql2 NVARCHAR(MAX);
EXEC sp_executesql @sql, N'@sql2 NVARCHAR(MAX) OUTPUT', @sql2 OUTPUT;
PRINT @sql2;
-- EXEC sp_executesql @sql2;
This is very rough, and lots of things can make it break. I could have combined the two selects into a single query but I think double-nested dynamic SQL was complex enough without adding conditional string concatenation in there as well.
It's possible that you're being bitten by this PgAdmin bug (changelog):
2012-11-28 AV 1.16.1 Date picker controls returns a full timestamp
by
default, which can cause inadvertent date changes
on jobs and role validty dates. Ignore the time part.
This bug has been seen to set password expiry dates far in the past, such as 1/1/1970. In this case the error message when trying to connect is no different than with a wrong password.
You can check these expiry dates with:
SELECT usename,valuntil FROM pg_user;
and if they're wrong, reset them with:
ALTER USER username VALID UNTIL 'infinity';
and upgrade pgAdmin.
Best Answer
At the SQL level, every user can indeed connect to a newly created database, until the following SQL command is issued:
Once done, each user or role that should be able to connect has to be granted explicitly the connect privilege:
Edit: In a multi-tenant scenario, more than just the
connect
privilege would be removed. For multi-tenancy tips and best practices, you may want to read on the postgresql public wiki: Shared Database Hosting and Managing rights in PostgreSQL.