Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to delete some databases in SQL Server 2008?

Posted on 2010-09-03
36
Medium Priority
?
635 Views
Last Modified: 2012-08-13
Dear EE,

I would like to ask for support on how to delete some databases on my SQL Server 2008.
This SQL Server is attached to our Sharepoint Server, and everytime I create a SSP, it generates a new database in whereas those hi-lighted items (as per attached image SQL1) are no more use, because the SSP of that database are deleted already.
That's why I would like to ask on how to delete, but everytime I delete it, the error from the image (SQL2) appears. I right-click then delete the item, but got that error.

Thank you & hope to hear soon!
SQLServerMgtStudio.jpg
SQLServerMgtStudio2.jpg
0
Comment
Question by:Stiebel Eltron
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 10
  • 7
  • +3
36 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33595223
could you post the actual error message "in texto"?
it looks like that it fails when trying to delete the backup history ...
0
 
LVL 11

Expert Comment

by:Coast-IT
ID: 33595233
You can use the following STSADM syntax to delete the sharedservices. In command propmt, go to "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\BIN"

STSADM -o deletessp -title "SharedServices1"

Before you delete the sharedservices, you have remove any sites within it, then only it allow you remove the sharedservices.
0
 
LVL 10

Expert Comment

by:shahzadbux
ID: 33595245
this is the stsadm command to delete ssp's, the -deletedatabases switch tells it to also delete the corresponding database on sql server


stsadm -o deletessp -title "" -force -deletedatabases
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 43

Expert Comment

by:Eugene Z
ID: 33595443
check - "close existing connection" - and to drop db faster   uncheck " Delete backup history.."
0
 
LVL 38

Expert Comment

by:Justin Smith
ID: 33595649
If the SSP has already been deleted then the STSADM command will do nothing.  It will fail.

This is a SQL issue.  Post the error as requested by angelll.
0
 

Author Comment

by:Stiebel Eltron
ID: 33597668
As per request by angel & Ach1lles, please see the attached image for the SQL Error result everytime I delete the database.

@shahzadbux: I did your instruction stsadm -o deletessp -title "<SSP Name>" -force -deletedatabases
but the SSP was the only item that was deleted, I checked the database from the SQL Server, it's still there. It wasn't deleted.
SQL-error-msg.jpg
0
 
LVL 38

Expert Comment

by:Justin Smith
ID: 33597686
i'm no sql expert but you probably don't have proper access on the database to delete.
0
 

Author Comment

by:Stiebel Eltron
ID: 33597853
@Ach1lles: Me too, I'm not expert with SQL that's why I'm looking for an expert to support me regarding this matter. Bec. I don't want everytime I create SSP & failed, a new database (useless database) will be created. I want to cleanup my SQL databases.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 33602941
additionaly to my 1st comment without what you may have problem to drop database that is in use..
you must have proper permissions to drop databases:
Permissions
DROP DATABASE permissions default to the database owner, members of the sysadmin and dbcreator fixed server roles, and are not transferable.
 
http://msdn.microsoft.com/en-us/library/aa258843(SQL.80).aspx
----
 
0
 

Author Comment

by:Stiebel Eltron
ID: 33603213
@EugeneZ:
check - "close existing connection" - and to drop db faster   uncheck " Delete backup history.."

Where to do the drop database syntax? CMD prompt or where?
0
 
LVL 43

Accepted Solution

by:
Eugene Z earned 1000 total points
ID: 33603614
you can use SSMS GUI:
on  your picture you can see in lower left corner   2 check boxes...
or
you can run script directly in SSMS

USE master
GO
ALTER DATABASE YourDB SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE master
GO
DROP DATABASE YourDB
GO
---\
if you need to run it as MS-Dos batch - > you can wrap it as sql file and execute via Sql server cmd command :
osql
sqlcmd
see more BOL
and\or
 http://www.sqlbook.com/SQL-Server/SQLCMD-command-line-utility-13.aspx
0
 

Author Comment

by:Stiebel Eltron
ID: 33608604
@EugeneZ:
Here's the result:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'IMMEDIATE'.
Msg 3701, Level 11, State 1, Line 1
Cannot drop the database 'SharedServices1_04092010', because it does not exist or you do not have permission.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 33608828
check your code:
please post it here for review
and make sure you have rights to drop database as well
0
 

Author Comment

by:Stiebel Eltron
ID: 33608871
I just copied this code:
USE master
GO
ALTER DATABASE YourDB SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE master
GO
DROP DATABASE YourDB
GO

RE: Rights to drop database, could u please advise how to know regarding the "RIGHTS"?
As I'm logging in using domain administrator account in SQL.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33609013
what database version/compatiblity level do you run that against?
0
 

Author Comment

by:Stiebel Eltron
ID: 33609263
@angellll: We're using SQL Server 2008 R2.
Compatibility level, sorry I'm not good working with SQL Server. Still crawling to know each details. :-)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33609339
you forgot the "SET" keyword:
http://technet.microsoft.com/en-us/library/bb522682.aspx
ALTER DATABASE YourDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

Open in new window

0
 

Author Comment

by:Stiebel Eltron
ID: 33609391
@angellll: I add up the keyword "SET", but still didn't work.
Please see the attached photos for the result image of it.

I think I'm having problem here regarding the permission with SQL. That's why maybe I can't pass with my Sharepoint SSP.
SQLServerMgtStudio3.jpg
SQLServerMgtStudio4.jpg
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33609467
that means the login you are using is not "sysadmin", aka does not have sysadmin server role ...
0
 

Author Comment

by:Stiebel Eltron
ID: 33609484
@angellll: I'm using Windows Authentication. I can't remember any UN &/or PW that I set for SQL Server.
If so, how can I create or do it?
Please advise...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33609505
>I'm using Windows Authentication.
so, you bascially use the "BUILTIN\ADMINSTRATOR" login on sql server level.
please double-check if that login has the sysadmin server role attached (normally, it has ...)
0
 

Author Comment

by:Stiebel Eltron
ID: 33609695
I saw this syntax: sp_addsrvrolemember [@loginame =] 'login', [@rolename =] 'role'
but don't know where to execute this syntax.
For the login name, do i need to put like this [DOMAIN\ADMINISTRATOR=] or just simply administrator?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33609713
you would run that like a normal sql
exec sp_addsrvrolemember '[DOMAIN\ADMINISTRATOR]', 'sysadm'

Open in new window

0
 

Author Comment

by:Stiebel Eltron
ID: 33609729
@angellll:
exec sp_addsrvrolemember '[STIEBELELTRON\ADMINISTRATOR]', 'sysadmin'

Got this error message:
Msg 15247, Level 16, State 1, Procedure sp_addsrvrolemember, Line 29
User does not have permission to perform this action.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 33609772
>User does not have permission to perform this action.

which double-confirms that your login you are using does not have the permission.
only a sysadm can grant that permission.

check out to have a login (aka sa) or some other login that has sysadm permissions to do the DROP DATABASE, or grant the permissions needed to your login.
0
 

Author Comment

by:Stiebel Eltron
ID: 33609856
@angellll: This is really weird! because earlier, I got error dropping some databases, right? That's why I asked you on how to create or add server roles to our administrator account. But after I closed the SQL Server then open it again, the databases that I want to delete or drop are gone! haha!!! How did it happen??? Weird! Now, I would like to add server roles to our BUILTIN\ADMINISTRATOR account. But still getting error...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33609891
can you try this:
when you drop the db, (and get the error), refresh the databases folder in your SQL Studio ...
the db might be dropped, actually, despite the "debug error" ...
0
 

Author Comment

by:Stiebel Eltron
ID: 33610076
Actually, I tried closing the SQLMS, and the databases that I want to delete are gone.
Now, I would like to add server roles to our BUILTIN\ADMINISTRATOR account. But still getting error...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33610160
you need to connect with "sa". your login won't be able to grant the permissions of sysadm, without having that permission itself.

if you cannot connect with non-windows account (of sa), check your sql instance, it's likely configured to allow windows authentication only. in which case, change, restart the instance, and try again.

of course, you will need to know the PW of the SA login ...
0
 

Author Comment

by:Stiebel Eltron
ID: 33610189
What is the default PW of the SA login? When I checked it, it's has a long characters. I don't have any idea for the PW of it...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33610213
>I don't have any idea for the PW of it...
well, then I fear you have to contact the real DBA of the instance.
you cannot "read/guess" the password, and there is no "default" password.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 33612142
"BUILTIN\ADMINSTRATOR -- is not in sql server 2008 by default anymore
you must contact somebody with sa role permisssions and ask to grant you with "drop db" rights - or drop db for you
 
To execute DROP DATABASE, at a minimum, a user must have CONTROL permission on the database.
0
 

Author Comment

by:Stiebel Eltron
ID: 33614621
@EugeneZ: If u can check my thread ID: 33609856, it's really weird that at the time I'm doing the advise of angellll, I got error. But after I closed the SQLMS, then open it again, the databases that I want to delete was gone. haha!!
Now, what I want to do is to add a server role (sysadmin role) to a certain account.

Which account can I add sysadmin & how?
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 33616643
hmm... you have permissions to delete db...
..account -- check what account you are using for the task and make sure it is member of SA role group
or create login as below article - just select check box sysadmin (#4):
 How to: Create a SQL Server Login
http://www.quackit.com/sql_server/tutorial/create_a_login.cfm 
0
 

Author Comment

by:Stiebel Eltron
ID: 33659594
@EugeneZ: I did this, reinstalled the whole system! lol!
Setup everything again, setup step by step until I got to create permissions @ SQL.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 33660865
wow- a long way ... whatever works
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question