Link to home
Start Free TrialLog in
Avatar of Stiebel Eltron
Stiebel EltronFlag for Thailand

asked on

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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

could you post the actual error message "in texto"?
it looks like that it fails when trying to delete the backup history ...
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.
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
check - "close existing connection" - and to drop db faster   uncheck " Delete backup history.."
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.
Avatar of Stiebel Eltron

ASKER

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
i'm no sql expert but you probably don't have proper access on the database to delete.
@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.
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
----
 
@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?
ASKER CERTIFIED SOLUTION
Avatar of EugeneZ
EugeneZ
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@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.
check your code:
please post it here for review
and make sure you have rights to drop database as well
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.
what database version/compatiblity level do you run that against?
@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. :-)
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

@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
that means the login you are using is not "sysadmin", aka does not have sysadmin server role ...
@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...
>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 ...)
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?
you would run that like a normal sql
exec sp_addsrvrolemember '[DOMAIN\ADMINISTRATOR]', 'sysadm'

Open in new window

@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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@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...
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" ...
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...
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 ...
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...
>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.
"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.
@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?
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 
@EugeneZ: I did this, reinstalled the whole system! lol!
Setup everything again, setup step by step until I got to create permissions @ SQL.
wow- a long way ... whatever works