Tech Off Thread

6 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Modified sp_MSdbuseraccess still shows offline databases...how to fix?

Back to Forum: Tech Off
  • User profile image
    mrichman

    I executed the modified sp_MSdbuseraccess system stored proc described in http://support.microsoft.com/default.aspx/kb/889696 but SQL Enterprise Manager still exposes offline databases that the user is denied access to.

    How would I further modify the above sproc to hide offline databases to which a user would otherwise be denied access?

    Thanks!

  • User profile image
    Maurits

    Hmmm... I would have thought this line would have taken care of that....

    (DATABASEPROPERTY(@qual, N'isoffline') <> 0) or

  • User profile image
    Maurits

    Oh, I see... that shows the database as grayed out.  You want to hide it altogether.

    Move this bit

          select @accessbit = has_dbaccess(@qual)
    if ( @accessbit <> 1) begin
    select @inval = 0x40000000
    select @inval
    return 0
    end
    Above this bit
          /* Can we access this database? */
    declare @single int
    select @single = DATABASEPROPERTY( @qual, N'issingleuser' )
    /* if ((@single <> 0) or ((@stat & SQLDMODBStat_Inaccessible) <> 0)) begin */
    if ((@single <> 0) or
    (DATABASEPROPERTY(@qual, N'isdetached') <> 0) or
    (DATABASEPROPERTY(@qual, N'isshutdown') <> 0) or
    (DATABASEPROPERTY(@qual, N'issuspect') <> 0) or
    (DATABASEPROPERTY(@qual, N'isoffline') <> 0) or
    (DATABASEPROPERTY(@qual, N'isinload') <> 0) or
    (DATABASEPROPERTY(@qual, N'isinrecovery') <> 0) or
    (DATABASEPROPERTY(@qual, N'isnotrecovered') <> 0)) begin
    select @inval = 0x80000000
    select @inval
    return 0
    end
    and that should do the trick.

  • User profile image
    mrichman

    Maurits wrote:
    Oh, I see... that shows the database as grayed out.  You want to hide it altogether.


    Yep, I want to hide it completely!

    Regarding the values for @inval (i.e. 0x40000000 & 0x80000000), would these be correct? (SQLDMO.h):

    SQLDMODbUserProf_InvalidLogin = 0x40000000,   // Current login not a valid user in this database.
    SQLDMODbUserProf_InaccessibleDb = 0x80000000, // Database Loading | Recovering | Suspect | Offline

    I'll give it a try...thanks!

    - Mark

  • User profile image
    Maurits

    mrichman wrote:

    SQLDMODbUserProf_InvalidLogin = 0x40000000,   // Current login not a valid user in this database.
    SQLDMODbUserProf_InaccessibleDb = 0x80000000, // Database Loading | Recovering | Suspect | Offline


    Since those are obviously combinable flags, I wonder if the "right" solution is to fix the stored procedure to return the combined results of the two checks... (changes in green)

    select @inval = 0

    /* Can we access this database? */
    declare @single int
    select @single = DATABASEPROPERTY( @qual, N'issingleuser' )
    /* if ((@single <> 0) or ((@stat & SQLDMODBStat_Inaccessible) <> 0)) begin */
    if ((@single <> 0) or
    (DATABASEPROPERTY(@qual, N'isdetached') <> 0) or
    (DATABASEPROPERTY(@qual, N'isshutdown') <> 0) or
    (DATABASEPROPERTY(@qual, N'issuspect') <> 0) or
    (DATABASEPROPERTY(@qual, N'isoffline') <> 0) or
    (DATABASEPROPERTY(@qual, N'isinload') <> 0) or
    (DATABASEPROPERTY(@qual, N'isinrecovery') <> 0) or
    (DATABASEPROPERTY(@qual, N'isnotrecovered') <> 0)) begin
    select @inval = 0x80000000
    -- select @inval
    -- return 0
    end

    select @accessbit = has_dbaccess(@qual)
          if ( @accessbit <> 1) begin
    -- select @inval = 0x40000000
    select @inval = @inval | 0x40000000
    -- select @inval
    return 0
    end

    if ( @inval <> 0) begin
    select @inval
    return 0
    end

  • User profile image
    Maurits

    Or... is it possible that has_dbaccess fails on offline databases?  Is the user-can-access-these-databases information stored in master, or in the particular databases?

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.