Finding Database Access details for currently logged user

Posted: May 6, 2013 by Virendra Yaduvanshi in Database Administrator
Tags: , , , ,

Sometimes it’s happened, a developer try to access a database but he/she could not get succeed and after few R&D he/she came to know that he/she has no access right for that particular DB. For same, a very useful SQL Server’s function HAS_DBACCESS can be used to get the list of all Databases having access details of currently logged user. Here is a simple script to get the details as

Select Namecase HAS_DBACCESS(namewhen 0 then ‘No Access’ else ‘Access’end AS DB_Access_Status from sys.databases

Note : if any Database is in RECOVERY Mode, it will shows as NO Access for that DB.

Advertisements
Comments
  1. Amol Raikar says:

    Thx sir ji , I want lot off information from u, can u help me?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s