Post reply

What are the essential PERMISSION GRANTS for making a FUNCTIONAL SQL database ?

  • January 17, 2008 at 4:31 pm

    #185752

    I am developing a web site with asp.net 2.0 and c#, using Visual Studio 2005 and Microsoft SQL Server 2005 ( I am still learning about these technologies and languages ).

    I transferred my web site files to my NEW computer.

    The web site worked fine when running inside Visual Studio, however when I tried to run it in its PUBLISHED format it was giving this error message:

    ~~~~~~~~~~~~~~~~~~~~~

    Server Error in '/MA' Application.

    Cannot open database "SiteData" requested by the login. The login failed.

    Login failed for user 'CENTAURUS\ASPNET'.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~

    I solved the problem by running SQL Server Management Stdio, right clicking “SiteData” database -> properties -> permissions->view server permissions then I chose “CENTAURUS\ASPNET” and since I DID NOT KNOW what grants are ESSENTIAL for the functioning of the database, I GRANTED EVERYTHING ( I ticked ALL the permissions).

    But since, probably, a lot of GRANTS are not only unnecessary but also may compromise the security of the web site, I would like to know what are the ESSENTIAL OR MINIMUM GRANTS NECESSARY for making the database functional to the web site and what GRANTS may compromise security.

  • January 17, 2008 at 9:05 pm

    #769535

    I'm thinking "simple answer" here... what were the grants on the old box?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 18, 2008 at 3:00 am

    #769582

    Hi Jeff

    I am not sure what you mean by “old box”. I would assume that you meant “old computer”.

    Actually I do not remember the settings in my old computer ( I threw it in the bin because it was broken ). But actually it does not really matter because, I made the settings in a intuitive / guessing manner and not one based on solid knowledge.

    The available list of grants in the SQL Server Management Studio is enormous so I will list just some of them so you can have a better idea what I am talking about :

    Alter any connection, Alter any credential, alter any database, alter any event notifgication,Alter any linkede server,Alter any login, Alter settings, Authenticate server,Connect SQL, Control server, create any datbase,shutdown,View any database,View any definition,View server state, etc.

    So I would like to know what the essential ones are and what should be granted only on exceptional situations.

  • January 18, 2008 at 7:33 am

    #769651

    Ah... understood...

    I'm pretty new to 2k5 and don't have an answer for you. Guess that'll be a good thing for me to study over the weekend... permissions.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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