• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 641
  • Last Modified:

How to delete some databases in SQL Server 2008?

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
Stiebel Eltron
Asked:
Stiebel Eltron
  • 15
  • 10
  • 7
  • +3
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
could you post the actual error message "in texto"?
it looks like that it fails when trying to delete the backup history ...
0
 
Coast-ITCommented:
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
 
shahzadbuxCommented:
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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
Eugene ZCommented:
check - "close existing connection" - and to drop db faster   uncheck " Delete backup history.."
0
 
Justin SmithSr. System EngineerCommented:
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
 
Stiebel EltronAuthor Commented:
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
 
Justin SmithSr. System EngineerCommented:
i'm no sql expert but you probably don't have proper access on the database to delete.
0
 
Stiebel EltronAuthor Commented:
@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
 
Eugene ZCommented:
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
 
Stiebel EltronAuthor Commented:
@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
 
Eugene ZCommented:
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
 
Stiebel EltronAuthor Commented:
@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
 
Eugene ZCommented:
check your code:
please post it here for review
and make sure you have rights to drop database as well
0
 
Stiebel EltronAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what database version/compatiblity level do you run that against?
0
 
Stiebel EltronAuthor Commented:
@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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Stiebel EltronAuthor Commented:
@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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that means the login you are using is not "sysadmin", aka does not have sysadmin server role ...
0
 
Stiebel EltronAuthor Commented:
@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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
Stiebel EltronAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you would run that like a normal sql
exec sp_addsrvrolemember '[DOMAIN\ADMINISTRATOR]', 'sysadm'

Open in new window

0
 
Stiebel EltronAuthor Commented:
@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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
Stiebel EltronAuthor Commented:
@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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Stiebel EltronAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Stiebel EltronAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
Eugene ZCommented:
"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
 
Stiebel EltronAuthor Commented:
@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
 
Eugene ZCommented:
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
 
Stiebel EltronAuthor Commented:
@EugeneZ: I did this, reinstalled the whole system! lol!
Setup everything again, setup step by step until I got to create permissions @ SQL.
0
 
Eugene ZCommented:
wow- a long way ... whatever works
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 15
  • 10
  • 7
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now