Solved

How to delete some databases in SQL Server 2008?

Posted on 2010-09-03
36
614 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
  • 15
  • 10
  • 7
  • +3
36 Comments
 
LVL 142

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
 
LVL 42

Expert Comment

by:EugeneZ
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 42

Expert Comment

by:EugeneZ
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 42

Accepted Solution

by:
EugeneZ earned 250 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 42

Expert Comment

by:EugeneZ
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 142

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 142

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 142

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 142

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 142

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 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 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 142

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 142

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 142

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 42

Expert Comment

by:EugeneZ
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 42

Expert Comment

by:EugeneZ
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 42

Expert Comment

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

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

SharePoint Designer 2010 has tools and commands to do everything that can be done with web parts in the browser, and then some – except uploading a web part straight into a page that is edited in SPD. So, can it be done? Scenario For a recent pr…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now