June 23, 2010 at 9:36 am
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
Users to drop databases? Why only in test environment?
That is the coolest idea I ever heard of!
😀
June 23, 2010 at 10:09 am
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
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!
June 23, 2010 at 11:45 am
:-):-)
🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply