What Permissions Are Needed To Drop All Connections On A Database?

Oct 6, 2006

I'm looking for the correct permissions to grant a user the ability to be able to drop all connections on a database.

The following code throws an exception:

Dim srv As New Server(SqlServerName)

If srv.Databases.Contains(siDBName) Then
srv.KillAllProcesses(siDBName) '== the srv.KillDatabase will not work here, we must kill the processes first
Dim db As Database = srv.Databases(siDBName)
db.Drop()
End If

Throws the Exception:
Microsoft.SqlServer.Management.Smo.FailedOperationException:
Drop all active database connections failed for Server 'xxxx'.
The user does not have permission to perform this action.

This code DOES NOT throw an exception when run as an Administrator.
Any Ideas?

View 4 Replies


ADVERTISEMENT

Drop Table Permissions

Jul 12, 2000

I have a group that has select, insert, update, delete permissions and I have a user in that group that
needs permissions also to drop tables.

does that login need to be aliased to dbo - which is in public -- do I then to give puboic all the other permissions.

Is there a way to just give one login all permissions including dropping tables??

Help!!

View 1 Replies View Related

Seems Like Permissions Issue With OLE DB Connections

Jun 14, 2006

Hello,

I've created quite few packages using OLE DB connections with dataflow destination as SQL Server Destination. The OLE DB connections using a windows login works fine when connecting to SQL Server Destination table(data flow destination) . The OLE DB connections using "sa" fails when connecting to the exact same table to "SQL Server Destination" but it works fine with "OLE DB Desination" even though it is the exact same underlining table.

Why is the "sa" login not working with SQL Server Destination table (data flow destination) in my OLE DB connection?

Thank you

View 13 Replies View Related

SQL Security :: Permissions To Create And Drop Tables?

Jul 31, 2015

what are the minimum permissions to allow a user to view, create, & drop tables within a DB (SQL 2008)?

View 4 Replies View Related

Granting A User Permissions To Create And Drop A Table

Nov 16, 2006

How do I allow a user (or group of users) permission to create/drop a table?

I have found the 'GRANT CREATE TABLE TO username' command, which will (I assume) allow a user to create a table, but how to I allow a user to 'DROP' the created table as well?
'GRANT DROP TABLE TO username' doesn't work?
and I want the users to be able to DROP/DELETE this table (temporary table created just for printing purposes) as well.

thanks

View 3 Replies View Related

Drop All Indexes In A Table, How To Drop All For User Tables In Database

Oct 9, 2006

Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob

View 2 Replies View Related

Help Needed In SSIS 2005 &&amp; Dialup Connections

Jun 29, 2006

In DTS 2000 there was situation where I had to connect source and destination through a dialup connection (56k), where the transferring of data took me ages to finish, so it was not successful.

In SSIS 2005 is there is a solution?

Thank you

Cheers,
Pradeep.

View 1 Replies View Related

SQL Security :: Deny Alter And Drop Permissions On ONLY Stored Procedures

Aug 19, 2015

We have a generic sql login "prduser". Applications use this login. We want the login NOT to have ALTER PROCEDURE and DROP PROCEDURE permissions only on the stored procedures(there are thousands of them).

View 17 Replies View Related

Permissions Needed To Run SSIS Package

Jul 23, 2006

If one of our SSIS packages fails because of a communication problem with the backend, and the DBA is not available, my boss wants another individual (probably a senior programmer but not an "sa" type) to be able to re-run the job.

What is the "right" way to do this under SSIS sql 2005?



TIA,

barkingdog

View 1 Replies View Related

Permissions Needed To Create A Shared Schedule?

Mar 10, 2008



I am setting up a role to allow certain users to create and maintain Shared Schedules in SSRS 2005. The tasks I have authorized are:
View reports
View folders
Manage all subscriptions
View data sources
Consume reports

What else do I need to grant? Right now the user cannot see the Site Settings link in order to get the the manage schedules.

Thanks,
Phill

View 1 Replies View Related

Permissions Needed To Deploy A Report Model

Jan 22, 2008

What permissions do I need on my SSRS server to deploy from VS2005. Currently I get the following error when I try to deply:



The permissions granted to user 'ReportServerNameUser' are insufficient for performing this operation.


I figure it is something simple that I am missing.

Thanks in advance!

Ustes

View 1 Replies View Related

Database Create ODBC Connections To Access Database Directly And Update Data?

Sep 10, 2012

We have a SQL database that uses Active Directory with Windows Authentication. Can users that are members of the Active Directory group that has read/write access to the SQL database create ODBC connections to access the database directly and update the data? They dont have individual logins on the server. They are only members of the Active Directory group that has a login?

View 1 Replies View Related

SQL Server 2008 :: Script To List All Database Level Permissions In A Database?

Aug 4, 2015

I found this script online..

SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission]
FROM [sys].[database_permissions] sec
JOIN [sys].[database_principals] prin
ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE sec.class = 0
ORDER BY [User], [Permission];

but the results are this: 2 columns - User and Permission

User Permission
User1 GRANT CONNECT
User2 GRANT CONNECT

IS there a way in SQL Server (2005/2008/2012) to run a script against a Database that will show all users that have permissions to that Database and the type of permissions?

View 7 Replies View Related

Trying To Drop A Database

Aug 31, 2000

I am trying to delete a database that is labeled suspect. Is there a way to force a drop of a database? It is shows it is marked inaccessible when trying to delete.
Thanks, Steve

View 1 Replies View Related

Drop Database

Nov 20, 2007

anyone know how to drop my databse on sql pleasee

charles

View 8 Replies View Related

Cannot Drop Database

Jul 23, 2005

I am trying to drop a database, but keep getting the following error."cannot drop database 'blah' because it is currently being used forreplication".This db is not currently being replicated, but once was. It is thesubscriber side of an old replication pair.Can anyone tell what I have to do to make this go away?Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Someone Drop A Database....

Jul 20, 2005

Hello everyone!I'm from El Salvador and i have a problem, i have a sybase systemrunning on windows 2000 server, probably a programmer drop adatabase...but i dont know who did it?, is it possible to know who dropthe database?, if the answer is yes... plese help me, because we need tosolve this security problem in our company.Sybase saves all the TSQL and transaction somewhere?,Saludos....--Posted via http://dbforums.com

View 1 Replies View Related

Drop All Tables In Database

Apr 25, 2007

what is the sql query to drop all tables in a database in sql server 2000

View 5 Replies View Related

Force Drop Database

Jul 9, 2004

Hi everybody,

I would like to know if there would be any special way to force drop a database from an ASP.NET page.

When I try to do it in the normal way, it gives me an exception like: Cannot drop the database 'xxxxxxx' because it is currently in use. I'would have to wait until there is a timeout.
In fact that database can be accessed from another pages, but I want to know if I'd be able to force drop database even when another pages are using it.



Thanks in advance

View 3 Replies View Related

Drop All Objects In Database?

Feb 14, 2006

I'd like to write a stored procedure to drop all objects in a SQL Server 2000 database owned by a particular uid. Originally I'd hoped to use these two stored proc built-ins for the task: sp_MScheck_uid_owns_anything (to get a list of all objects owned by a uid) and sp_MSdrop_object (to drop the objects). I've run into a few problems along the way:

1. If I run this command

EXEC sp_MScheck_uid_owns_anything 5

I get this weird error message:

"The user owns objects in the database and cannot be dropped."

Not sure why that is since I'm just trying to list the objects, not drop them.

2. I tried running a simple query to get the objects from the system table instead:

SELECT * from [dbo].[sysobjects] where uid = 5

This returns a resultSet as you'd expect. When I wrote a stored procedure to loop through these and use sp_MSdrop_object it seemed to fail whenever it encountered a foreign key object. Here is the error message:

The request for procedure 'name of foreign key' failed because 'name of foreign key' is a unknown type object.

Can anyone give advice as to the best way to go about doing this? I'd really prefer not to have to drop the entire database and recreate it. Thanks!


-Cliff

View 7 Replies View Related

How To Drop All PKs On Tables In Database?

Feb 1, 2007

I have a list of 35 tables that need to drop the primary key index from in my database.

My problem is as follows for these 35 tables:

1. How can I get a list of all the primary keys for this subset of tables in my database
2. How can I drop just the PK for each of these tables?

I want an easy quick way to do this without having to manually do this for each of the 35 tables in my database. I dont want to do this for all tables just the subset.

Thanks

View 9 Replies View Related

Drop All Database Indexes

Apr 6, 2004

How can I drop all the indexes in all the tables in a MS 2000 SQL Server?

View 9 Replies View Related

How To Drop A Database From Specified Location

Jun 2, 2008

Hi i m Uday,i want to delete ie Drop a database from specified location
for this i used this query as
DROP DATABASE AdventureWorks1,'C:Sql_dB_creatorsqlDB_creatorApp_DataAdventureWorks1'
but i gives incorrect syntax error.

So plz give the sql query to Drop a Database from specified path

Thank You

uday

View 3 Replies View Related

Drop All Database Objects

Feb 3, 2006

Does anyone happen to have a script that will drop all database objects?

I'm looking for something generic that will work with any SQL Server 2000 database. I can write one myself, but I thought I would check here first.

Thanks in advance

John

View 2 Replies View Related

Drop Database Problem

Jul 26, 2007

Hi All,

I use SQL SERVER 2005 and SQL SERVER Mobile.
My SQL Server I created publication, and on mobile subscriber and did web Synchronization , no problem.

I deleted publication from under Local Publication with SQL SERVER Managment Studio.
and now I want drop database, I get this error "Drop failed for Database DBNAME ... Cannot drop database DBNAME because it is being using Replication"

How to I drop this database ?
And where registred this replication information ?



Regards,
Mahir Quluzade

View 1 Replies View Related

Can Not Drop User From Database

Oct 12, 2005

 I can not delete user from a database in sql2005 beta 3.

View 22 Replies View Related

Drop Database On A Different SQL Server.

Sep 6, 2007



Hi,

I am new to SQL Server 2005.Till now, I have been using a SP to execute DROP DATABASE command to drop databases on my existing database server.
but now i want to delete a database which is on a different SQL Server 2005 instance on a different machine. but i am not sure how to do this.
Can anyone please help me on this?
Any help would be appreciated.

Thanx in advance.
Kawal

View 4 Replies View Related

Database Connections

May 25, 2006

Hi all,

I am fairly new to mobile development and im trying to write a simple application that can read, write from a SQL Server Mobile Database and load data into a list view. The problem im having is trying to find examples on how to do this and i cant seem to get many of the ones i find to work properly. Im still use to recordsets and adodb and if anyone has any examples on how to do this that would be great.

Also, would it possible if someone could explain this code to me as well

 Dim conn As SqlCeConnection = Nothing
Dim cmd As SqlCeCommand = Nothing
Dim rdr As SqlCeDataReader = Nothing

Try
' Open the connection and create a SQL command
'
conn = New SqlCeConnection("Data Source = AdventureWorks.sdf")
conn.Open()

cmd = New SqlCeCommand("SELECT * FROM DimEmployee", conn)

rdr = cmd.ExecuteReader()

' Iterate through the results
'
While rdr.Read()
Dim employeeID As Integer = rdr.GetInt32(0) ' or: rdr["EmployeeKey"];
Dim lastName As String = rdr.GetString(5) ' or: rdr["FirstName"];
End While

' Always dispose data readers and commands as soon as practicable
'
rdr.Close()
cmd.Dispose()
Finally
' Close the connection when no longer needed
'
conn.Close()
End Try


Thanks

 

byeadon

View 1 Replies View Related

DROP DATABASE -&&> Database In Use Error

Jul 12, 2006

when I do DROP DATABASE I'm getting errors because it's in use. But if I do a delete from the mgmt studio gui I have the option to close all connections as part of the delete. Is there a way to achieve the same effect from a sql script?

View 7 Replies View Related

Drop Database Fails Because Of Aspnet_wp.exe

Jun 22, 2006

Hey everyone.

View 2 Replies View Related

Sql Server Database Drop Down List

Jun 27, 2006

I was wondering if anyone may be familiar with a way to create a dropdown list that included a list of SQLServers.  Basically I want to create a little application that lets a user point to an instance of sql server, similar to enterprise manager.  I wanted to use C# and figured there may be some namespaces I could use that provide this functionality but was not sure.
Thanks,

View 2 Replies View Related

Not Able To Drop Database After Opening Datatable

May 3, 2005

Hi everybody,
I am using VB Webforms as my front end and MSDE 2000 as my back end.  I am not able to drop a database after executing the following vb code on that database:
        Dim vConnection As New SqlConnection(vConnectionString)        Dim vAdapter As New SqlDataAdapter("SELECT party_type_code, party_type_name, party_type_imported" & _                                         " FROM Party_Type" & _                                         " ORDER BY party_type_name", vConnection)        Dim vDataTable As New DataTable()        vAdapter.Fill(vDataTable)        vAdapter.Dispose()        vConnection.Dispose()
On trying to drop the database using the SQL Command "DROP DATABASE PPCABCD2005", I get the error message:Cannot drop the database 'PPCABCD2005' because it is currently in use ...However, if I don't execute the above code, I am able to drop it.  Why is it so?  What is the solution to this problem?

View 2 Replies View Related

Restore Database --&> Drop Users

Nov 25, 2005

Third question:
I'm backing up a database which has some users.
When I try to restore it to another machine which has the same users and already has an old version of the database the database cannot be accessed. I must drop the users from the database by using the stored procedures sp_dropuser <username> and then add it again to the database from the Enterprise Manager.

Why this happens??

Regards,
Manolis

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved