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%'
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%'
Thanks Visakh.
ReplyDeleteIT will tell about only Databases but how you tell which user for which database?
The given code will check for the access right in various databases in a server for the current logged in user. As you see HAS_DBACCESS() doesnt have a passed argument so it will always use current logged in user as the context.
ReplyDelete