Sunday, May 5, 2013

Checking database access rights for a user in a sqlserver instance

There was a recent requirement in one of my projects to check the access for particular user in all the databases in a server. Thought of sharing the script used through the blog for quick reference.
 The solution makes use of HAS_DBACCESS function available in SQL Server. The script would be as below


SELECT name, CASE HAS_DBACCESS(name) WHEN 1 THEN 'Has Access' ELSE 'No Access' END AS Access
FROM sys.databases

The catalog view sys.databases will have a record for each database within the instance. The above script will give as the output a list of databases with their access information based on result HAS_DBACCESS returns. It will return 1 for all databases accessible to current user and 0 otherwise.
The system databases can be filtered by adding a condition such as below to get only the user databases information.


SELECT name, CASE HAS_DBACCESS(name) WHEN 1 THEN 'Has Access' ELSE 'No Access' END AS Access
FROM sys.databases
WHERE  name NOT IN ('master','tempdb','model','msdb')
AND name NOT LIKE 'ReportServer%'