Database access in Microsoft SQL Server

Jigar Zanzarukiya 0

I am using HAS_DBACCESS function to check if user has access to the database present in sys.databases. Customer is claiming that they have given access to all the database present in server but while check it using HAS_DBACCESS it is returning 0 (zero) as output. Can someone explain me which all permission HAS_DBACCESSS function check to decide if user has access on database or not? Am I missing anything about HAS_DBACCESS function?

Which permission customer need to give to the user so that this function return 1 as output?

CathyJi-MSFT 22,306 Reputation points Microsoft Vendor

2024-07-30T01:49:36.58+00:00

Hello @Jigar Zanzarukiya ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it is not work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
CathyJi-MSFT 22,306 Reputation points Microsoft Vendor

2024-08-02T06:38:01.6333333+00:00

Hello @Jigar Zanzarukiya

Any update for this case? Did the response could help you?

3 answers

Erland Sommarskog 112.7K Reputation points MVP

2024-07-26T13:20:57.2566667+00:00
To have access to a database, a server login must:

Map to a user in the database.

This user must have CONNECT permission.

If you add a login to a database, you get CONNECT permission, but this permission can be revoked or denied.

My guess is that the customer is confused when they say that they have given access.

You can also test this way:

USE db go EXECUTE AS USER = 'UserToTest' go REVERT

This should complete without errors if the user has access.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Zahid Butt 956 Reputation points

2024-07-26T13:31:15.6733333+00:00

Hi,

In this case customer needs to give 'membership in the public role' on requested database to that user.

For further info please refer to:

https://learn.microsoft.com/en-us/sql/t-sql/functions/has-dbaccess-transact-sql?view=sql-server-ver16

In security->Users right click on target user->Properties->User Mapping-> check Target database & ok:
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
CathyJi-MSFT 22,306 Reputation points Microsoft Vendor

2024-07-29T03:11:29.53+00:00
Hello @Jigar Zanzarukiya ,

The HAS_DBACCESS function checks several conditions to determine if a user has access to a specified database. Here are the key points it considers:

Database State: The database must not be offline or in a suspect state.

User Mapping: The user must be mapped to a login that has access to the database.

Database Mode: The database should be in multi-user mode, or if in single-user mode, the current user must be the single user. In restricted-user mode, the user must be a member of the dbcreator or sysadmin fixed server roles, or the db_owner fixed database role.

If these conditions are met, HAS_DBACCESS returns 1, indicating the user has access. Otherwise, it returns 0 or NULL if the database name is invalid.

Please check these conditions whether users meet?

Below query will list all the database the current logged in user has access to:

SELECT name, HAS_DBACCESS(name) HasAccess FROM sys.databases
Please sign in to rate this answer.
Jigar Zanzarukiya 0 Reputation points

2024-07-29T04:07:10.08+00:00

Is there any way I can check this 3 prerequisite Database state, User mapping & Database mode using SQL query?

CathyJi-MSFT 22,306 Reputation points Microsoft Vendor

2024-07-29T05:18:24.63+00:00

hello @Jigar Zanzarukiya ,

Please using below scripts to check database status and database mode;

SELECT name, state_desc,user_access_desc FROM sys.databases;

Using SSMS UI to check user mapping

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Jigar Zanzarukiya 0 Reputation points

2024-07-30T06:33:08.3+00:00

I checked status & mode is ONLINE & MULTI_USER. I can't use UI to check public role member ship on client DB. Is there any SQL Query way to check if user has public role membership or not on that database.

CathyJi-MSFT 22,306 Reputation points Microsoft Vendor

2024-07-30T07:02:41.1266667+00:00

Hello @Jigar Zanzarukiya ,

Please try below query:

SET NOCOUNT ON CREATE TABLE #temp ( SERVER_name SYSNAME NULL , Database_name SYSNAME NULL , UserName SYSNAME , GroupName SYSNAME , LoginName SYSNAME NULL , DefDBName SYSNAME NULL , DefSchemaName SYSNAME NULL , UserID INT , [SID] VARBINARY(85) ) DECLARE @command VARCHAR(MAX) --this will contain all the databases (and their sizes!) --on a server DECLARE @databases TABLE ( Database_name VARCHAR(128) , Database_size INT , remarks VARCHAR(255) ) INSERT INTO @databases--stock the table with the list of databases EXEC sp_databases SELECT @command = COALESCE(@command, '') + ' USE ' + database_name + ' insert into #temp (UserName,GroupName, LoginName, DefDBName, DefSchemaName,UserID,[SID]) Execute sp_helpuser UPDATE #TEMP SET database_name=DB_NAME(), server_name=@@ServerName where database_name is null ' FROM @databases EXECUTE ( @command ) SELECT loginname , UserName , Database_name FROM #temp WHERE LoginName = 'your login name'

Erland Sommarskog 112.7K Reputation points MVP

2024-07-30T07:34:08.6233333+00:00

Is there any SQL Query way to check if user has public role membership or not on that database.

No, because all users in a database are members of public and you cannot remove a user from that group.

But you can easily check if a user has been granted access to a database at all:

SELECT * FROM sys.database_principals WHERE sid = suser_sid('loginname')

(Replace loginname with the actual name for the login.)

Furthermore, as I said before, the user needs to have CONNECT permission to the database. When you add a user to a database, the user gets the CONNECT permission automatically. However, it can be revoked or denied. You can check for the permission this way:

SELECT * FROM sys.database_permissions p JOIN sys.database_principals u ON p.grantee_principal_id = u.principal_id WHERE p.permission_name = 'CONNECT' AND p.state_desc = 'GRANT' AND u.sid = suser_sid('loginname')
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Database access in Microsoft SQL Server

3 answers

Your answer