What permissions have your users really got?

Here’s a TSQL script to audit the permissions of certain AD users access to a SQL Server instance. This script uses the EXECUTE AS LOGIN clause and the system function sys.fn_my_permissions. All databases on the SQL Server instance are queried and the script will output results containing the assigned user permissions. To get started all you need to do is change the INSERT into #users to contain the users you want to audit.
Check out this post if you want to audit users in a particular AD group. It might save you a little more time.

CREATE TABLE #users
(
	username VARCHAR(50) NOT NULL PRIMARY KEY CLUSTERED
);

INSERT INTO #users
(
	username
)
VALUES('DOMAIN\user1'),
('DOMAIN\user2'),
('DOMAIN\user3');

CREATE TABLE #permission_results
(
	username VARCHAR(50) NOT NULL,
	[database] VARCHAR(50) NOT NULL,
	[entity_name] VARCHAR(50) NOT NULL,
	subentity_name VARCHAR(50) NOT NULL,
	[permission_name] VARCHAR(50) NOT NULL
);

DECLARE user_cursor CURSOR FOR SELECT [username]
							   FROM #users;

DECLARE @username VARCHAR(50),
		@sql NVARCHAR(4000);

-- Open the cursor and get the first result
OPEN user_cursor;
FETCH NEXT FROM user_cursor INTO @username;

WHILE (@@FETCH_STATUS = 0)
BEGIN

	DECLARE db_cursor CURSOR FOR SELECT [name]
								 FROM sys.databases;
	DECLARE @database_name VARCHAR(50);

	OPEN db_cursor;
	FETCH NEXT FROM db_cursor INTO @database_name;

	WHILE (@@FETCH_STATUS = 0)
	BEGIN

		SET @sql = 'USE [' + @database_name + '];
					EXECUTE AS LOGIN = ''' + @username + ''';
					INSERT INTO #permission_results
					SELECT SUSER_NAME(), DB_NAME(), * FROM sys.fn_my_permissions (''?'', ''DATABASE'' );
					REVERT;'; -- Revert to avoid error

		BEGIN TRY

			EXEC sp_executesql @sql;

		END TRY
		BEGIN CATCH

			-- If we hit here then the current user probably doesn't have access
			DECLARE @error_message VARCHAR(1000);
			SET @error_message = ERROR_MESSAGE();
			PRINT 'Current user = ' + @username + '.' + @error_message;

		END CATCH;

		FETCH NEXT FROM db_cursor INTO @database_name;

	END;

	-- Clean up db_cursor
	CLOSE db_cursor;
	DEALLOCATE db_cursor;

	FETCH NEXT FROM user_cursor INTO @username;

END

-- Clean up
CLOSE user_cursor;
DEALLOCATE user_cursor;

SELECT *
FROM #permission_results
ORDER BY username;

DROP TABLE #users;
DROP TABLE #permission_results;

If the user does not have access to a database then this will not be included in the output result-set. Instead a message will be printed;

The server principal "DOMAIN\user1" is not able to access the database "model" under the current security context.

The output will look something like this;

DOMAIN\user1	master	database CONNECT
DOMAIN\user1	master	database SHOWPLAN
DOMAIN\user1	master	database VIEW DATABASE STATE
DOMAIN\user1	userdb1	database CONNECT
DOMAIN\user1	userdb1	database SHOWPLAN
DOMAIN\user1	userdb1	database SELECT
DOMAIN\user1	userdb1	database INSERT
DOMAIN\user1	userdb1	database UPDATE
DOMAIN\user1	userdb1	database DELETE
DOMAIN\user1	userdb1	database EXECUTE
DOMAIN\user1	userdb1	database VIEW DATABASE STATE