Exclusive User Access To Database
Sep 29, 2007
Hi All,
My question is regarding SQL Server database security. I want to create a login using SQL Server Authentication and assign it db_owner rights for my database. So far so good. But the critical part is that I want to give exclusive rights for my database to this user only i.e. no other users (dbo, guest etc) should be allowed to access my database.
It will be good to present here the scenario which I need to implement. I am running an ASP.NET application that uses SQL server 2005 db at back end. The database server might have other databases as well but I don't want the administrator (either SQL server admin or the server administrator) to be able to get access to my database or even view the schema. I don't want any other user except my own user to be able to detach the database or perform backup or restore operations.
I hope I was able to deliver the requirement.
View 6 Replies
ADVERTISEMENT
Jul 12, 2004
Hi.
I need to access a database to modify, updates,... massively . It's possible to lock a database and have exclusive access?
(SQLServer 2000)
thanks.
Francisco
View 2 Replies
View Related
May 25, 2004
How to close the existing connections to a particluar database in sql server. Please note that i donot want to start stop sql server. I just want to close the existing connections so that i can do a restore on that database programatically.
I am using sqldmo for this purpose. Does anyone knows how to do that with sqldmo or is there any other method??
Waiting for your earliest replies
View 1 Replies
View Related
May 31, 2008
Hi!
We're using a backup with sql server agent when doing a backup / restore
procedure. In some cases I get the following error when the restore job
fails:
Executed as user: DOMAINAdministrator. Exclusive access could not be
obtained because the database is in use. [SQLSTATE 42000] (Error 3101)
RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).
The step failed.
Is it related to unhandled lock? I've resolved this by restarting the sql
server. But is there a way to avoid such issues?
One more question. Is it OK to backup/restore database while there're users
connected? Or I can do only backup?
View 2 Replies
View Related
Apr 11, 2007
Hi
I am trying to use sql server 2005 management stodio to restore a database. But I got this error message:
Exclusive access could not be obtained because the database is in use
Anyone know how to solve this problem?
Thanks
Li
View 45 Replies
View Related
Jun 15, 2007
I have a test database that is automatically restored from the live database each day at 2:30AM. I recently started receiving this message:
Exclusive access could not be obtained because the database is in use.
and the restore is not able to occur.
Any ideas on how I can prevent this from happening?
View 1 Replies
View Related
Apr 11, 2007
Hi all. I have read and implemented the very helpful threads on backing up and restoring with user instance posted with MFriedlander. Thank you. However, during the rs.SqlRestore(srv) command I am getting the following error "Restore failed for Server '\.pipe4A1F91FF-F6FE-45 sqlquery'. "
"Exclusive access could not be obtained because the database is in use."
I have implemented the changedatabase method as described in that thread.
SqlConnection.ChangeDatabase("master")
right before the line
rs.SqlRestore(srv)
I do not use the default instance of sql express, but I do use an instance called 'test' for my app. Should my changedatabase method also refer to my sql instance?
I am running from VS 2005 debug (F5) when it fails and cannot think of anything that would be locking it. Any help would be appreciated. Thank you. Below is the full code of the restore.
Robert
Dim sqlconnection As SqlConnection = New SqlConnection(My.Settings.dbTestConnectionString)
sqlconnection.Open()
MsgBox(sqlconnection.Database.ToString())
Dim ServerConnection As ServerConnection = New ServerConnection(sqlconnection)
Dim srv As Server = New Server(ServerConnection)
'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.
Dim bdi As BackupDeviceItem
bdi = New BackupDeviceItem(txtRestoreFile.Text, DeviceType.File)
''Define a Restore object variable.
Dim rs As New Restore
'Set the NoRecovery property to true, so the transactions are not recovered.
rs.NoRecovery = True
rs.ReplaceDatabase = True
'Add the device that contains the full database backup to the Restore object.
rs.Devices.Add(bdi)
'don't know why the below norecovery is changed to false
rs.NoRecovery = False
'Specify the database name.
rs.Database = sqlconnection.Database.ToString()
sqlconnection.ChangeDatabase("master")
'Restore the full database backup with no recovery.
rs.SqlRestore(srv)
View 4 Replies
View Related
Feb 5, 2003
Hi,
i need to run a restore of a database overnight onto a different server using the live data .bak file. however the job failed on the first run (last night) with the error:
"Exclusive access could not be obtained because the database is in use. ...."
how do i gain this Exclusive use via an SQL statement please?
View 8 Replies
View Related
Feb 11, 2004
I have created a SQL Agent job that is supposed to essentially duplicate a production database to another database. The code I am using is:
step1
__________________________________________________ ______
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+'kill '+cast(spid as varchar)+char(13)+char(10)
from sysprocesses where dbid=12
--Print (@SQL)
exec(@sql)
step2
__________________________________________________ ________
RESTORE DATABASE HIWDYNARPT FROM PRDBACKUP
WITH REPLACE
__________________________________________________ ______
This works when I test it during the day, however when it runs at night I get the following error in the job log:
Database in use. The system administrator must have exclusive use of the database to run the restore operation. [SQLSTATE 42000] (Error 3101) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
I'm not sure why this happens because I have killed all open threads in step 1, and then create my own new thread in step two. Maybe someone else is initiating a new thread to quickly between the steps???
Anyway, I am trying to use:
__________________________________________________ __
ALTER DATABASE HIWDYNARPT
RESTRICTED_USER
WITH ROLLBACK IMMEDIATE
__________________________________________________ ____
...as an alternative to the T-SQL killing PID's, but SQL 7.0 SP4 does not seem to support restricted user like 2000. It keeps giving me a syntax error. Does anyone have any suggestions?
If I bring step 1 and step 2 together, separated by "GO", could this fix the problem?
Thanks in advance!
Ryan Hunt
View 5 Replies
View Related
Dec 8, 2007
Could anybody give a lead as to what I can do get rid off this error please.
I alread tried following:
use master
go
Alter Database dbname set single_user with rollback immediate;
go
Still have the issue. SQL 2005 Server actually did lock the db.
So ran
Alter Database dbname set multi_user;
go
and refresh Query and it switch back to multi user.
But I can't restore db yet.
Thank you
View 1 Replies
View Related
May 5, 2008
Purpose
I would like to know if it is possible and if so how to implement exclusive access to a given record within in a database table.
Example
For example, you can open a file system file with exclusive access so other processes cannot access the file until it is released. I would like to implement a solution that provides this same exclusive access at the database record level. Preferably a solution that does not require creating and managing table access state flags. (i.e. €“ FieldName.State = (Closed, Opened, Exclusive)) I would like for the database server to mange this solution and return an error status when trying to access a record that has been opened exclusively. Furthermore, I would like to avoid using triggers.
Application Utilization
I€™m working on a system that has multiple threads that are each responsible managing data from the same normalized table. Each of these system threads work on the same unique record in parallel. Therefore it is possible that thread (0)€™s changes could be overwritten by thread (n)€™s. I would like to have each thread open the unique record exclusively and release it when finished. All of the other threads would loop until the record is released.
Questions
1. Is it possible to implement this at the server level?
2. What would be the easiest best practice for implementing this functional requirement?
Thanks,
Sean
View 5 Replies
View Related
Jul 7, 2007
Hi everyone,
Hope somebody can help me on this.
I did a full BACKUP for two SQL databases using SQL Server Managament Studio. When trying to RESTORE the DBs, I get the following error:
"System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)"
There are NO users using the application. These are DBs for the Portfolio Server 2007 application.
After exploring a little bit, I found that there are several "SLEEPING" processes with an "AWATING COMMAND" flag. The processes come from the Portfolio Server Application, maybe from previous sessions.
Can they be deleted? if so, would there be any serious consequences?
Thank you for your help on this.
Oscar E.
View 4 Replies
View Related
Mar 25, 2008
I need to determine the following about the current authenticated Windows domain user who is trying to access a SQL Server via a trusted connection.
1 Has the current user been granted login access to the trusted SQL Server?
2 Has the current user been granted access to a specific database?
3 Is the current user a member of a specific database role such as (DB_ROLE_ADMINISTRATORS)?
Thanks,
Sean
View 6 Replies
View Related
Aug 10, 2015
Any list of commands that require exclusive access in order for the command to complete? I had an instance today where a DBA executed sp_changedbowner command which is the alter database command on a production database and it locked it up.
View 0 Replies
View Related
Jul 7, 2000
When I try to give a login access to a database I get the message:
Error 21002: [SQL-DMO] users already exist in the database.
I checked sysusers for the database and the users name is in there, even if the user has no login.
Is it okay to just delete the record that has the users name from sysusers?
Thanks
View 1 Replies
View Related
Jun 11, 2007
Downloaded and install SQL 2005 on 2003 member server. Downloaded and ran SQLServerSamples.msi, AdventureWorksBI.msi, AdventureWorksDB.msi, and AdventureWorksLT.msi.
When I go into Sql Server Management Studio I cannot find the Adventure Works database under databases. How do I access it?
Thank you.
View 3 Replies
View Related
Sep 11, 2007
Please help....
I have designed an application that uses sql server 2005 express database. This application was intended to be installed and used on a local desktop machine. However, now this application needs to be also installed on several computers and still needs to use only 1 database. Part of the database saves local user settings, so what I have done thus far is use two connection strings: one for local database (because the application may be used as origionally intended) and one for remote database that everyone should be able to connect to.
When I remotely connect to the database, it works fine. However, it seems to be locked so when I chose the same database for another user I get the error message "This File Is In Use." I need to know how to unlock it and make it usable for several users at the same time.
Thank you for your help! Please let me know if you need more information or if something is unclear in my description.
View 6 Replies
View Related
Feb 10, 2014
I am newbie to SQL.I need to create an application will run on server, and of course will be installed by using admin user. I can use the install user to access to database on that server?
View 1 Replies
View Related
May 26, 2008
Hi friends,
I have created a database DB1 using CREATE DATABASE DB1 command. Then i created login name using CREATE LOGIN login1 WITH PASSWORD = 'password1' command and created user name using CREATE USER user1 FOR LOGIN login1 command. Now i have to assign the user1 to the database DB1.
Any one please tell me how to assign DB1 access privilege to user1?
Thanks in Advance
Sathish kumar D
View 1 Replies
View Related
Mar 17, 2008
Hi,
How do I create a user that has full access to a database using only SQL statements?
I've create very basic database called "sampledb".
Then I've done:
CREATE LOGIN sampleuser WITH PASSWORD = '123sampleuser', DEFAULT_DATABASE = sampledb;
USE sampledb;
CREATE sampleuser FOR LOGIN sampleuser WITH DEFAULT_SCHEMA = 'db_owner';
Then I logon to SQL server express 2005 management studio using this user, first of all I do not see the sampledb database listed under "databases" even though it is selected in the "available databases" toolbar (how do I list it there? Because going through "attach..." tells me I do not have the privileges to browse for the database to attach).
I then open up a new query (sampledb is selected), and type (products is one of the table in sampledb):
select * from products;
And I get:
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'Products', database 'sampledb', schema 'dbo'.
Shouldn't the chosen schema (db_owner) give me the right to do this? And how do I fix this from the query window (without going through the GUI, just using SQL statements)? And how do I list sampledb under the databases when I log in as this user?
Thank you!
-Yannick
View 1 Replies
View Related
Mar 3, 2008
Hi,
I am using SQL Server Express Edition 2005 as a backend database working with Visual Basic 2005.
I am using Vista and having two users to access to my computer. User-1 and User-2.
I created a database in User-1 and works fine Visual Basic 2005.
Now the problem is when i login to my computer system with User-2. I cannot able to access the database with encountering error like "User-1/SQLExpress". I know that i cannot able to access to database which was created in User-1.
Do you any solution to this problem. when i login with user-1 and user-2 it should able to access database.
Thanks.
Regards
Kashif Chotu
View 1 Replies
View Related
Oct 12, 2006
/* -------------------------------------------------
The Following Procedure Helps You To
Send a Messge to all Users
those Who Access a Specified Database in a Server
*/
Create Proc Sp_SendMsg
@Msg Varchar(100),
@DbName VarChar(100)
as
if db_id(@DbName) = Null
begin
Print 'DataBase dose not Exist'
end
else
Begin
Declare @spId Varchar(1000)
DECLARE TmpCursor CURSOR FOR
Select 'master..xp_CmdShell ''Net Send ' + rtrim(convert(Varchar(50), HostName))+' "Dear ' + upper(max(rtrim(loginame)))+ space(1) + @Msg + '"''' as spId
from master..SysProcesses
where db_Name(dbID) = @DbName and
spId <> @@SpId
and rtrim(ltrim(HostName)) <> ''
and HostName <> host_Name()
and dbID <> 0
and spId > 50
group by HostName
OPEN TmpCursor
FETCH NEXT FROM TmpCursor
INTO @spId
WHILE @@FETCH_STATUS = 0
BEGIN
Exec (@spId)
FETCH NEXT FROM TmpCursor
INTO @spId
END
CLOSE TmpCursor
DEALLOCATE TmpCursor
end
View 2 Replies
View Related
Aug 23, 2004
Dear All,
I want my SQL database to be accessed always in a single user mode through my application. How can I do this ?
Thanks in advance.
Sushma
View 5 Replies
View Related
Apr 22, 2014
Currently there are various teams accessing the database. For costing reasons, we need to track usage.Is there an efficient way to monitor User access to the database.Can we track which user has executed which query(SELECT,insert etc),the login time and such parammeters?
View 5 Replies
View Related
Apr 26, 2008
Hi all,
I have a SSIS package that copies data from one database, into another, makes a backup, and then FTP's up the backup to a web server. In Sql Server I've made a job to run the package and a Stored procedure to activate the job from an Access database front-end.
Can anyone tell me how I might return a success/failure message to my access user?
Thanks
Melt
View 2 Replies
View Related
Sep 24, 2013
I'm trying to grant a user group select access to all the views in a database. I already made a query which creates a result set whit the SQL Syntax I (displayed below) need but it seems to be impossible to get this result set executed after creation.
use [AdventureWorksDW2008R2]
SELECT
'GRANT SELECT ON [' + SCHEMA_NAME(Schema_id) + '].[' + name + '] TO [DOMAINGROUP]'
FROM sys.views;
View 5 Replies
View Related
Jul 28, 2015
How do I grant a user permissions to only one table in a database.  How would it affect him using our Main App which is NAV with regards to his user's permission in NAV
View 2 Replies
View Related
Feb 7, 2008
I have a restriced user on SQL Server that is only permitted to creat a new database and manage it only. All other database are hidden to that user.
when that user login to SQL Server and create a database and try to change the default folder path for data and log files, gets an error,
The Server principal "User" is not able to access the database "model" under the current security context. (MSSQL Server, Error: 916)
Any idea???
Thanks,
View 7 Replies
View Related
Oct 6, 2015
someone was alter user  databases owner access.
how to find  who alter evoke  the database user access changes in sql server.
View 5 Replies
View Related
Aug 20, 2007
Hi, I hope you can help.I have configured a Windows 2003 web server and SQL 2005 Server (on same box) to successfully allow remote connections and to allow access via SQL Server Management Studio Express 2005.The problem I have is that I want to restrict access to the databases on the server via the Management Studio to specific databases e.g. 1 database user "sees" only 1 database.I can configure it so that the user's remote access permissions do not allow access to other databases but they can still "see" the database listed in the Management Studio explorer.I can also configure it so that the users cannot see all the databases (by disabling View All Databases on SQL Server), but this means that they cannot not see their own database which they have permissions for.Is it impossible to have the desired behaviour of only displaying the database which the remote user accessing has permissions for and hiding all other databases?I have MSN'd,Googled and Yahoo'd this one to no avail :(Many thanksFergus
View 6 Replies
View Related
Jan 31, 2008
Hi,
How would I set permission for SQL Server 2005 "User A" to prevent access to System and other user databases, also How to hide the databases that "User A" has no rights to. I mean, when User A logs in, All other user databases are not visible to him/her.
Thanks,
View 6 Replies
View Related
Dec 18, 2007
We are using Crystal Report Services XI to generate reports from SQL Server 2000 database running on server within the same domain (LAN).
for some reason, SQL Server is rejecting the Credentials that is provided to CR Services, the user has right to that database. When We run the report manual, it works fine, but when we run Automatic on schedule , it fails.
The Authentication mode is set to Mixed.
The Startup Service accoutn is set to "This Account"
Any idea, why would this failure on User is encoutered.
Thanks,
View 1 Replies
View Related
Dec 6, 2013
I would like to know if there is a way to find out who changed a users roles/access WITHOUT using the audit function. For example, if a user account was created and given SA access then changed to read only, how can I find out who made that change? I tried searching for an answer, but kept getting no results. I'm thinking this may tie into the sys.sysusers view?
View 3 Replies
View Related
