List of Available Database for Current User In SQL Server

Introduction

When working in the SQL Server, we may have to check some other databases other than the current one which we are working. In that scenario we may not be sure that does we have access to those Databases?. In this article we discuss the list of databases that are available for the current logged user in SQL Server

Get the list of database

We get the list of databases from the system table sys.databases

SELECT * FROM sys.databases 
Fig.1 List of Databases from sys.databases table

Now we have to check does the user has access by applying the function HAS_DBACCESS. It returns 1 if the user has access to the database, 0 if the user has no access to the database, and NULL if the database name is not valid. When this function returns 0 that it also means that the database is offline, single-user mode, or any other issues that are preventing us to use the database.

SELECT 
	database_id AS DataBaseId, 
	Name AS DBName, 
	HAS_DBACCESS(Name) AS HasAccess,
	is_read_only as IsReadOnly,
	create_date AS DbCreatedDate
FROM sys.databases 
Fig. 2 List of DB for Current Logged User

From the above query,

  • database_id – Database id
  • Name – Name of the database
  • HAS_DBACCESS(Name) Has access or not, if 1 then has access, 0 means not having access or may be other issues
  • is_read_only – Shows is read only access or not. If 1 then user have read only permission, if 0 then has both read and write.

Conclusion

In this article, we have discussed about how to check the list of available databases for the logged user. I hope this article was useful. Please share the feedback in the comment section.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

Website Built with WordPress.com.

Up ↑

%d bloggers like this: