Post reply

Need help creating login/user with correct privileges

  • July 28, 2010 at 5:54 am

    #225518

    Hi,

    I'm trying to understand how to create a login and user with the correct privileges, but I'm having trouble (I'm new to much of this).

    My goal: From a stored proc, I need to restore a database from a backup (.bak) to a staging database, perform 'select' operations on the staging database, then drop the staging db. The .bak is received from a business partner on a nightly basis, and the process must be fully automated via the stored proc (the process will be run overnight).

    My question: How can I create a server login & db user that has the correct level of privileges to create, restore, select, and then drop the staging database, all from a stored proc? Everything works fine under the 'sa' account of course, but that's not acceptable. Also, if possible, I'd like this user to only have access to the staging database, no other database.

    I've tried creating a login with dbcreator privileges, but then I run into problems with some ALTER statements in the script. Everything I try seems to hit a dead end.

    I'd greatly appreciate any help.

    Thanks.

  • July 28, 2010 at 5:11 pm

    #1199538

    You might like to look into the EXECUTE AS clause in your stored procedure where you would run under an account with high level permissions. You would then grant EXECUTE permissions on this stored procedure to your user, do not allow them to alter the procedure as they would then be able to run other code that you may not want them to do.

    Basically you would be letting your user run the sp under the context of another user, but they would have no control over the workings of it. If you can ensure this it will be secure.

    Hope this makes sense.

    Rich

    Hope this helps,
    Rich

    [p]
    [/p]

  • July 28, 2010 at 7:58 pm

    #1199562

    Great idea, that does make sense. I will give it a try. Thanks!

  • August 9, 2010 at 4:30 pm

    #1204444

    While 'execute as' is useful at times, it does not work if you are referencing objects in another database.

    An easy way is to create the procedure. Test it. Then at the end of it, type

    Go

    Grant execute on procnamehere to usernamehere

    The nice thing about stored procedures as you decide what they can do within the confines of the stored procedure.

    hope this helps too.

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

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