Check if a user has access to a database in Sql Server 2005

HAS_DBACCESS returns information about whether the user has access to the specified database (BOL).

Example:

SELECT HAS_DBACCESS('Northwind');

returns

1 if the user has access to the database
0 if the user does not have access to the database
NULL if the database does not exist

Find all databases that the current user has access to

SELECT [Name] as DatabaseName from master.dbo.sysdatabases
WHERE ISNULL(HAS_DBACCESS ([Name]),0)=1
ORDER BY [Name]

This query was written by a guy named safigi in sqlteamforums.


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

6 comments:

Unknown said...

Other best alternative of the above command is you can use sp_helplogins without paramter for checking the existing user permissions and the database list


ex
1. sp_helplogins
2. sp_helplogins 'LoginName'


Regards
shashi kant chauhan

Anonymous said...

Thanks for tip.

Anonymous said...

How would you verify a remote user hass access to a database. In our environment our users are worldwide. Sometimes local configuration and local networks can mimic database access issues. What would be a simple test we could ask a user to perform to verify their access to the database is NOT the issue. Is there a script for something like this?

Jonathan Kehayias (MCITP, MVP) said...

In an international implementation, the easiest way to validate that a login isn't the specific problem would be to create a simple Login Validation web page that accepts the username and password, and opens a connection to the database, and then closes it. If the connection is successful then the login isn't the issue. I'd put this on a server that is guaranteed to not have issues talking to the SQL Server, preferably on the same VLAN as the SQL Server. If .NET was used, then it would be very easy to read any resulting Exception and cast the inner exception as a SqlException which will contain a SqlError collection that can be built into an error message. The following C# Example would be a good base for a .Net Tester:

public class TestConnect
{
public TestConnect(string servername, string username, string password)
{
SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
sb.DataSource = servername;
sb.UserID = username;
sb.Password = password;

Console.WriteLine(TestConnect(sb.ConnectionString));
}

public TestConnect(string servername)
{
SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
sb.DataSource = servername;
sb.IntegratedSecurity = true;

Console.WriteLine(TestConnect(sb.ConnectionString));
}

public string TestConnect(string connectionstring)
{
try
{
SqlConnection conn = new SqlConnection(connectionstring);
conn.Open();
return "Successful";
}
catch (Exception ex)
{
System.Data.SqlClient.SqlException se = (System.Data.SqlClient.SqlException)ex.InnerException;

string errors = string.Empty;

if (se.Errors.Count > 0)
{
foreach (System.Data.SqlClient.SqlError sqlerr in se.Errors)
{
errors += "Msg " + sqlerr.Number.ToString() + ", Level " + sqlerr.Class.ToString() + ", State " + sqlerr.State.ToString() + ", Line " + sqlerr.LineNumber.ToString() + "\r\n" + sqlerr.Message + "\r\n\r\n";
}
}
return errors;
}
}
}


The other option would be to drop to the command line and use sqlcmd to determine what the problem might be:

sqlcmd -S servername -U username -P password

or for windows authentication

sqlcmd -S servername -E

If the login is the problem, then they will get back a 18456 message from the SQL Server which is a login failure message.

Msg 18456, Level 14, State 1, Server servername, Line 1
Login failed for user 'username'.

If the problem is something else, like they can't reach the server, then a named pipes provider error similar to the following would be returned:

HResult 0x35, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [53].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

If they don't have the SQL Server Client Tools installed, then the above won't work. I am sure I could write a vbscript that would trap errors and report back the state similar to one of the above implementations.

Unknown said...

SteveH, what is meaning of this 'Sometimes local configuration and local networks can mimic database access issues'. jonathan kehayias (MCITP) can i use your code on my app.

Jonathan Kehayias (MCITP, MVP) said...

kirti,

Absolutely. I wouldn't have put it in a comment if it couldn't be used. Make sure that you add a reference in the class to System.Data.SqlClient, or it won't work at all.