March 06, 2008

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.


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

6 Responses to "Check if a user has access to a database in Sql Server 2005"
  1. shashi said...
    March 28, 2008 at 11:59 PM

    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

  2. Anonymous said...
    March 29, 2008 at 4:50 AM

    Thanks for tip.

  3. Steve H said...
    September 11, 2008 at 5:33 AM

    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?

  4. Jonathan Kehayias (MCITP) said...
    September 13, 2008 at 4:48 PM

    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.

  5. kirti said...
    September 13, 2008 at 7:35 PM

    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.

  6. Jonathan Kehayias (MCITP) said...
    September 14, 2008 at 9:39 AM

    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.

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions