i need a sql statment or procedure to kill all user connection to one database.
And Deny new connection for a little time.
So i can a restore this database from a dump.
I can this do only in exclusiv modus. without any other connection.
I have few SQL user who has permissions on different databases. When they are accessing MSSQL server from Enterprise Manager they can see all Databases but can not access them and they can change file size allocated to them. I want to restrict this and they can only see database belongs to respective user and restrict user to change allocated size. Please help me with this as I am not able to find the solution.
I have a user who is Business owner of a database that is transactional.
At times some of here staff will be the leadblocker.
She has requested that she be allowed the abiity to KILL spids that are blocking her remaining users. All have blessed this process and I havve been tasked to create a way for this to be accomplished.
Is this as simple as allowing this user a permission or is it more in depth.
Hello,I do maintenance on the Back endand have like 10 - 20 connections open...specialized scrips i run and theydont need to be stored proc'sis there a way to kill the connection when the script is finished...from myclient side.....not just disconnect...cause server still has the pool of the connection...iwant to kill the pool'd connection alsothanksDave P
OS: windows server 2003 database version: sql server 2005
i want to kill user process.when i tried to kill user process using its spid... kill 57 i got error SPID 57: transaction rollback in progress. Estimated rollback completion: 52%. Estimated time remaining: 448373 seconds. i also found that this process has suspended status and wait_type is BACKUPTHREAD one more thing i wait to complete rollback transcation since last 3 days. but after 3 days getting same error and estimated time increase every time i issue KILL command. even i tried activity monitor to kill process.but no luck is there any way to slove this issue??? how to kill this process.?? well i havnt try to stop and restart sql server processes.because its producation server.will database complete shudown and server process restart help to overcome this issue. your help is really appreciate. thanks, saurabh
OS: windows server 2003 database version: sql server 2005
i want to kill user process.when i tried to kill user process using its spid... kill 57 i got error SPID 57: transaction rollback in progress. Estimated rollback completion: 52%. Estimated time remaining: 448373 seconds. i also found that this process has suspended status and wait_type is BACKUPTHREAD one more thing i wait to complete rollback transcation since last 3 days. but after 3 days getting same error and estimated time increase every time i issue KILL command. even i tried activity monitor to kill process.but no luck is there any way to slove this issue??? how to kill this process.?? well i havnt try to stop and restart sql server processes.because its producation server.will database complete shudown and server process restart help to overcome this issue. your help is really appreciate. thanks, saurabh
OS: windows server 2003 database version: sql server 2005
i want to kill user process.when i tried to kill user process using its spid... kill 57 i got error SPID 57: transaction rollback in progress. Estimated rollback completion: 52%. Estimated time remaining: 448373 seconds. i also found that this process has suspended status and wait_type is BACKUPTHREAD one more thing i wait to complete rollback transcation since last 3 days. but after 3 days getting same error and estimated time increase every time i issue KILL command. even i tried activity monitor to kill process.but no luck is there any way to slove this issue??? how to kill this process.?? well i havnt try to stop and restart sql server processes.because its producation server.will database complete shudown and server process restart help to overcome this issue. your help is really appreciate. thanks, saurabh
I am using SQL Server 7.0. We have set up a task which needs exclusive use of database 'EMS_DB' . How do I schedule a task to run kill all processes in 'EMS_DB' just before this task. Any TSQL scripts for killing all precesses for a user database?
I am working with an application unde mssql 6.5 ,sp4. We have a separate database for reporting on a different box. Each night we load a backup into the database from the production database.
I set up a stored procedure to kill all users connected to this reporting database just before the load.
THe job looks like this under scheduled tasks as a cmdexec:
Process Exit Code 1. ...[-b On error batch abort] [-O use Old ISQL behavior disables the following]<EOF> batch processingAuto console width scalingWide messagesdefault errorlevel is -1 vs 1 [-? show syntax summary (this screen)]
The killusersall is paramaterize sp that I pass the dbid of the database for whom I want all users killed. I need to put the stored procedure in the master because I egt the spid from sysprocesses.
I do not understand why the job died. Does it matter if I named the stored procedure as sp_killusersall instead of killusersall_sp which I used.
Can any one advise me about how to get the job to run? Any assistance will be greatly appreciated. Thanks you.
Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache
I am a bit new to the MSSQL server. In our application, we use so many SQL queries. To imporve the performance, we used the Database enigine Tuning tool to create the indexes. The older version of the application supports MSSQL 2000 also. To re-create these new indexes, I have an issue in running these "CREATE INDEX" commands as the statements generated for index creation are done in MSSQL 2005. The statements include "INCLUDES" keyword which is supported in MSSQL 2005 but not in MSSQL 2000.
Ex:-
CREATE INDEX IND_001_PPM_PA ON PPM_PROCESS_ACTIVITY
Ben writes "I have a sql script that doesn't function very well when it's executed on a SQL 2000 server.
The scrpt looks like this:
--------------------------------------------------------------------------------------------------- USE [master] GO IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME') EXEC sp_addlogin N'SSDBUSERNAME', N'SSDBPASSWORD' GO GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME] GO GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME] GO GRANT CONNECT SQL TO [SSDBUSERNAME] GO GRANT CONTROL SERVER TO [SSDBUSERNAME] GO GRANT CREATE ANY DATABASE TO [SSDBUSERNAME] GO USE [master] GO If EXISTS (Select * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME') ALTER LOGIN [SSDBUSERNAME] WITH PASSWORD=N'SSDBPASSWORD' GO GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME] GO GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME] GO GRANT CONNECT SQL TO [SSDBUSERNAME] GO GRANT CONTROL SERVER TO [SSDBUSERNAME] GO GRANT CREATE ANY DATABASE TO [SSDBUSERNAME] GO USE [master] GO IF EXISTS (select * from dbo.sysdatabases where name = 'ISIZ') DROP DATABASE [ISIZ] GO USE [SurveyData] GO exec sp_adduser 'SSDBUSERNAME' GRANT INSERT, UPDATE, SELECT, DELETE TO SSDBUSERNAME GO USE [SurveyManagement] GO exec sp_adduser 'SSDBUSERNAME' GRANT INSERT, UPDATE, SELECT, DELETE TO SSDBUSERNAME ---------------------------------------------------------------
I need to be converted to a script that can be executed on both MSSQL 2000 and MSSQL 2005.
I was wondering if somebody there could help me with this problem?!
I've been tasked to move our production databases on MSSQL 2000 to 2005. I've supported MSSQL since version 6.5 and performed migrations to successor versions.
Current Environment is MSSQL 2000 32-bit with current Service Packs.
I've performed mock migrations on Test servers upgrading all Production instances simultaneously from MSSQL 2000 to 2005 32-bit. The Test environment is identical to Production minus server name, IP etc. Also I have a separate server with MSSQL 2005 installed where I use the DETACH / ATTACH and BACKUP / RESTORE method for migration / acceptance testing. There are approximately 30 databases totaling 70 GB. This has gone as expected and fairly successful. Vendors have been coordinated with to update code and staff for acceptance testing.
I'd prefer going directly to MSSQL 2005 64-bit instead if possible due to memory benefits etc. This is where I'd like some feedback prior to borrowing a 64-bit server for testing.
Upgrade options:
1. Is it better to migrate from MSSQL 2000 32-bit to 2005 64-bit via: a. DETACH / ATTACH b. BACKUP / RESTORE c. Is one method more advantageous relating to the end result? 2. Regarding XP clients, have issues been experienced with the default SQL Server driver or is an alternate recommended for XP clients to connect to a MSSQL 64-bit server databases? 3. If you have performed this migration and have relevant experience please pass them along.
This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database) Help pleaseee
Dear all I need your help in order to restore a database.
I did a backup of one SQL database hosted on my web service, and after I copy the file on my C:Wutemp disk.
I'd like to import this database on my MSSQL installed on my local pc in order to have the same web site in local.
I try to restore it with enterprise manager (..using Restore) but I receive a lot of error message: (I translate from Italian so..be patient..)
The filename 'C:Program FilesMicrosoft SQL ServerMSSQLDatamioDB_DATA.mdf' could be not correct. Impossible to restore the file 'miodb_Data' in 'C:Program FilesMicorosft SQL ServerMSSQLDatamioDB_DATA.mdf'. Use WITH MOVE in oreder to identify a valid location for the file The filename 'C:Program FilesMicrosoft SQL ServerMSSQLDatamioDB_Log.LDF'could be not correct Impossible to restore the file 'miodb_Log' in 'C:Program FilesMicorosft SQL ServerMSSQLDatamioDB_Log.ldf'.
I need an application with asp.net that let me restore database by getting .bak file and database name and database user and user password, my database is SQL Server 2005.
Hi. I've following problem: - Customer played with his logical drives in a CPQ smart array - He played too much: he lost the drive with the LDF-File - Now the SQL-Server is in a kind of wired mode; you can start and stop it, but you cannot connect to (neiter 'sa' nor any other user available before) In fact the mode is not wired: what shall he do other when most important file is missing... :o - The problem for me now is:
> How can I get the Enterprise Manager or Query Analyzer up and running again to start a restore?
PS: The restore is to be done from a tape-library running Veritas Backup Exec + SQL-Agent
PPS: Error-log says (what I already know): 2005-10-04 17:18:02.69 spid11 Device activation error. The physical file name 'H:DEVLOG1DEVLOG1.ldf' may be incorrect.
I'm trying to kill a bunch of processes in SQL 6.5 and I can't. I'm running the only machine with SQL tools installed on it (the server) and it won't let me kill them. I try the GUI screens and the Kill statement in ISQL_w. Is there any way around this?
I've stopped the SQL Server and rebooted the NT Server. Is there anyway I can get rid of these processes. They are locking some tables and keeping me from inserting data within my code. Very frustrating.
Hi, I have a copy of VS2005 and i have installed the Microsoft SQL Server Developer Edition I am trying to do my first connection using ASP.net (yay for me...) and it has failed...with this error..Login failed for user 'testuser'. The user is not associated with a trusted SQL Server connection.
can someone please point out why? yes i know that the error seems pretty straight forward but how do i fix it? like how do i make the user a trusted server connection? where do i do it? and what are the steps? the user is an administrator and by rights should have all access to everything....
connection string
SqlConnection MySQLConnection; MySQLConnection = new SqlConnection("server=cmptrname\databsename;uid=testuser;password=password;database=mytest");
btw while your reading do you have any examples of opening a db with the connection string in the web.config file? because i get some error "connection string has not been initialised" using the system.Configuration.ConfigurationManager.AppSettings.Get i though i would stick to the top code first before i tackled the web config error first....
All the research I found dealing with this problem is that the solution is to set SQLserver to mix mode. I have SQL server already set to mix mode. I am not sure what else to do. Has anybody run into this problem? my connection string: <connectionStrings> <add name="ConnectionString" connectionString="Data Source= server name ; Initial Catalog=FILESHAREDB.MDF; Integrated Security=false;"providerName="System.Data.SqlClient"/> </connectionStrings>
Server Error in '/SendItNow' Application.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Login failed for user ''. The user is not associated with a trusted SQL Server connection.Source Error:
Line 41: Line 42: 'open the connection and execute the stored procedure Line 43: mConnection.Open() Line 44: mResourceID = mCommand.ExecuteScalar() Line 45: mConnection.Close()Source File: H:MIS DepartmentIntranetSendItNowApp_CodeDalResourceDB.vb Line: 43 Stack Trace:
Hi all, I am new in asp.net. In my web.config file, I wrote the connection strings as below and then I got the above error. I already changed SQL server Authentication mode to "SQL server and Windows". Any idea? Thanks in advance. <connectionStrings> <remove name="LocalSqlServer" /> <add name="LocalSqlServer" connectionString="Data Source=xxx;Initial Catalog=mydb;User ID=test;Password=test;Persist Security Info=False;" providerName="System.Data.SqlClient" /> </connectionStrings>
We have setup a SCOM 2007 Server and in this we have SQL 2005 SP2, We are using two accounts one for OPRATIONAL DATABASE and another one for REPORTING DATABASE after this setup everything was working fine but suddenly my SDK account which is for REPROTING DATABASE was locked by entering wrong password and afterwards I unlocked the password but SINCE this i'm not able to connect to my REPORTING DATABASE and i'm getting following ERROR:- Login Failed for user 'username'. The user is not associated with a trusted SQL Server connection.
Taken following action to rectify this problem: 1) Resetting the SDK password with the same password. 2) Restarted all the SQL and SCOM services. 3) Restarted the Server as well. 4) Converted from WINDOWS AUTHENTICATION mode to MIXED mode.
But the problem is still persisting, Please help ASAP.
I received the above error yesterday and haven't been able to trace it to any job or process running. We haven't implemented any changes to the server in the past few months, and it doesn't look to be a user-established connection, since the Client IP Address of the SSPI handshake error is from the server itself.
I logged this set of messages in SQL AgentServer error log:
Date 10.03.2008 6:15:19 PM Log SQL Server (Current - 11.03.2008 2:32:00 PM) Source Logon Message SSPI handshake failed with error code 0x80090304 while establishing a connection with integrated security; the connection has been closed. [CLIENT: <IP Address>]