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

Exclusive Access To DataBase

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

Exclusive Access Could Not Be Obtained Because The Database Is In Use.

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

Exclusive Access Could Not Be Obtained Because The Database Is In Use

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

Exclusive Access Could Not Be Obtained Because The Database Is In Use

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

Exclusive Access Could Not Be Obtained Because The Database Is In Use.

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

More On Restoring Database In User Instance - Exclusive Use Not Obtained

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

How To Obtain Exclusive Access Via SQL

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

Trying To Get Exclusive Access To A DB For Restore.

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

Exclusive Access Could Not Be Obtained Because The

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

How To: Implement Exclusive Access To A Given Record.

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

Cant Restore SQL Server Databases: Exclusive Access Could Not Be Obtained

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

How To: Determine If Current Windows User Has Login Access, Database Access And If They Are A Member Of A Specific DB Role.

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

SQL Server 2008 :: List Of Commands That Require Exclusive Access In Order For Command To Complete

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

User Database Access

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

Cannot Access User Database

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

Multi-User Access Of SQL Database

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

Access To Database By Local Admin User

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

How To Assign Database Access Privilege To User?

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

Creating A User That Has Full Access To A Database

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

Logging With Another System User Cannot Access My Database

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

Send A Messege To User Those Who Access A Specified Database In A Server

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

How To Access Database In Single User Mode All Time

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

SQL 2012 :: Efficient Way To Monitor User Access To Database

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

Returning Success Message To User - Access Database

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

Grant User Group Select Access To All Views In Database

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

Data Access :: Grant A User Permissions To Only One Table In A Database?

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

User Is Not Able To Access The Database Model Under The Current Security Context

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

SQL Security :: How To Find Who Alter Database User Access Changes In Server

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

SQL Database User Account Access Remotely Via SQL Management Studio Express

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

Restricting Data Access To System Database And Visibility To All Other User Databases...!

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

User Is Rejected To Access Database From Crystal Report Client And Services...!

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

SQL Server 2012 :: Query To Find User Who Last Modified User Roles / Access?

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







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