Post reply

DROP DATABASE

  • June 23, 2010 at 9:36 am

    #223175

    A request has been made for some users to be able to drop databases from a test server themselves, rather than contacting IS. We plan to do this through our portal where they can go to the page, choose a database and click the DROP button. I am trying to setup a stored procedure to accomplish this, but I am having trouble setting up a parameter for the database name. Here is my script

    CREATE PROC [dbo].[DELETE_DATABASE](

    @DATABASENAME VARCHAR(255)

    )

    AS

    BEGIN

    SET NOCOUNT ON;

    DROP DATABASE [@DATABASENAME]

    GO;

    END

    I would prefer to create this procedure on our custom script database, but it seems like I will have to create it on master. There are also a few databases on this test server that I would like to prevent them from having the rights to drop.

    Thanks for the help.

  • June 23, 2010 at 9:55 am

    #1184048

    Users to drop databases? Why only in test environment?

    That is the coolest idea I ever heard of!

    😀

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • June 23, 2010 at 10:09 am

    #1184058

    Got it!

    CREATE PROC [dbo].[DELETE_DATABASE](

    @DATABASENAME VARCHAR(255)

    )

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(100)

    SET @sql = N'DROP DATABASE ' + @DATABASENAME

    EXEC sp_executesql @sql;

    END

  • June 23, 2010 at 11:18 am

    #1184108

    I guess the next challenge would be to create the sp for users to formatt hard drive or/and re-install Windows and SQLServer. 😀

    You have quite demanding users, man!

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • June 23, 2010 at 11:45 am

    #1184116

    :-):-)

    🙂

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

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