Post reply

Permisison for taking DB on and offline

  • September 8, 2009 at 3:58 pm

    #137989

    I have a sql server login that is able to take a DB offline and bring it back online. The login does not have any server roles only rights to certain DBs. On some of them it has dbo rights, but not the one in question. Am I missing something? I would think that if a user doesn't have any rights on the DB, the user can't take it offline.

    As I'm writing this, I decided to try something. I brought it back online and tried to query a table and I was able to do so. Clearly, I'm missing a permission somewhere, I just don't know where to look. Everything looks locked down.

    Any help would be greatly appreciated.

  • September 9, 2009 at 4:32 am

    #1050953

    try this and see what it returns

    the first select will give you the server permissions for the account

    the second select will give you the permissions within that database for that account

    execute as login= 'Login'

    SELECT * FROM fn_my_permissions(NULL, 'SERVER');

    use MyDatabase

    SELECT * FROM fn_my_permissions (NULL, 'DATABASE');

    revert

    see if the account has the alter permission

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

  • September 9, 2009 at 7:18 am

    #1051065

    Thanks for the query. Exactly what I needed.

    Turns out the login in question was set at the owner of the DB. It seems that by giving a login ownership of the DB it had implicit rights to the database and can perform any action. What was stumping me was that the login was not listed in the security tab.

  • September 9, 2009 at 7:42 am

    #1051107

    J.D. Gonzalez (9/9/2009)

    Thanks for the query. Exactly what I needed.

    Turns out the login in question was set at the owner of the DB. It seems that by giving a login ownership of the DB it had implicit rights to the database and can perform any action. What was stumping me was that the login was not listed in the security tab.

    You will find that a lot to be honest, there are different ways of seeing permissions and the GUI does not always show everything.

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply