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
ADVERTISEMENT
Oct 30, 2007
Hi guys,
I've been developing desktop client-server and web apps and have used Access and SQL Server Standard most of the time.
I'm looking into using SQL CE, and had a few questions that I can't seem to get a clear picture on:
- The documentation for CE says that it supports 256 simultaneous connections and offers the Isolation levels, Transactions, Locking, etc with a 4GB DB. But most people say that CE is strictly a single-user DB and should not be used as a DB Server.
Could CE be extended for use as a multi-user DB Server by creating a custom server such as a .NET Remoting Server hosted through a Windows Service (or any other custom host) on a machine whereby the CE DB would run in-process with this server on the machine which would then be accessed by multiple users from multiple machines??
Clients PCs -> Server PC hosting Remoting Service -> ADO.NET -> SQL CE
- and further more can we use Enterprise Services (Serviced Components) to connect to SQL CE and further extend this model to offer a pure high-quality DB Server?
Clients PCs -> Server PC hosting Remoting Service -> Enterprise Services -> ADO.NET -> SQL CE
Seems quite doable to me, but I may be wrong..please let me know either ways
Thanks,
CP
View 3 Replies
View Related
Oct 25, 2006
I have developed two programs that operates against the same database. One of the program is just a display program that displayes the data put into the other administation program where you put in the data to be displayed (an administration program).
Every time I access the db from the administration program, the display program stops and throws connection pool errors and other database errors. For me, it looks like it does´t do multi-access to a database.
I have tried putting user instance to off in both programs, but this didn´t help.
Connection string also points to same file database.
View 6 Replies
View Related
Feb 27, 2008
I am using SharePoint Services 3.0 (SP1) with default configuration options, which installs the Microsoft##SSEE instance of SQL to my local C: drive.
While attempting to relocate the files to another drive, I set one of the databases (as recommended) to Single User by using the SQL Server Management Express tool.
I cannot now reset that database to Multi User, even by executing the query
exec sp_dboption 'database_name', 'single user', ''FALSE'
again by using the Management Express Tool.
Can someone please help, in plain english???? Thanks
View 5 Replies
View Related
Mar 22, 2012
I am trying to place a database offline.
When I right in Microsoft SQL Server Management Studio on the database > Take Offline
Set offline failed for Database 'OperationsManagerAC'.
Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 1205)
So I am trying with some queries to pass the database from Single user to multi user but it fails
USE [master]
GO
ALTER DATABASE OperationsManagerAC SET MULTI_USER GO
Msg 1205, Level 13, State 68, Line 1</p><p>Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
I tried also
alter DATABASE OperationsManagerAC SET OFFLINE WITH ROLLBACK IMMEDIATE
IT FAILS AS WELL... with the same error... how to run the SQL Statements without using
How to proceed next?
View 12 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
Jul 10, 2007
Okay, I've read massive topics on ClickOnce, and embedded database applications, etc, etc.
I need a handful of good suggestions how I can create a deployment package, to get my sql express database onto a SQL Express server.
The database will need to be multi-user, because I am also developing a WinForms application to connect to the SQL Express database.
I'm thinking I need to use some automated scripting features, to generate scripts for the database once I am done (unless you have a better suggestion).
After that, I need to know what to do with those scripts, and how can I create a setup / deployment packages to run those scripts against a SQL Express server.
The other alterntative obviously is to copy my sql express database, and run an attach command. This will work the first time, only because this will have been the first deployment of this database.
Please keep in mind when answering this question, that I will not be embedding the database, and as far as I understand ClickOnce is a feature for use with embedded databases or user instance databases (not sure).
Daniel Crane
View 6 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 23, 2014
Disaster Recovery Options based on the following criteria.
--Currently running SQL 2012 standard edition
--We have 18000 databases (same schema across databases)- majority of databases are less than 2gb-- across 64 instances approximately
--Recovery needs to happen within 1 hour (Not sure that this is realistic
-- We are building a new data center and building dr from the ground up.
What I have looked into is:
1. Transactional Replication: Too Much Data Not viable
2. AlwaysOn Availability Groups (Need enterprise) Again too many databases and would have to upgrade all instances
3. Log Shipping is a viable option and the only one I can come up with that would work right now. Might be a management nightmare but with this many databases probably all options with be a nightmare.
View 1 Replies
View Related
Jun 21, 2004
I have written a VB/SQL Server based application. The application uses stored procs and the SQL Server security model to handle logins and permissions. My partner and I want to sell the applicaiton to specific businesses and offer a rental program for smaller companies that would like to use the application to connect to an internet based database. The application uses TCP/IP with a specific port number to connect to the database so it can be used in or out of LAN.
If we rent the application, maintenance is simple. I just create a user in the database with the login/password the user would like and viola they have access to the data.
When a company wants to purchase the program to use in-house, we would like to offer two alternatives: #1 a single user or #2 a multiple user SQL Server database.
My question is if a company purchases the single-user version, how can we keep them from adding users to their database. In other words, since we have no control over the database at the client's location, how can we ensure that it remains a single-user database?
Thanks for any help!
Randall Medcalf
View 1 Replies
View Related
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
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
Dec 5, 2007
Dear all,
to restore one perticular backup set, i've made the database to single user mode, now it is not accepting me to connect to that again...
how can i bring the database to multiuser mode?
thanks in advance
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 9 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
Mar 27, 2007
I am new to Reporting Services and hope that what I am looking to do is within capabilities :-)
I have many identical schema databases residing on a number of data servers. These support individual clients accessing them via a web interface. What I need to be able to do is run reports across all of the databases. So the layout is:
Dataserver A
Database A1
Database A2
Database A3
Dataserver B
Database B1
Database B2
Dataserver C
Database C1
Database C2
Database C3
I would like to run a report that pulls table data from A1, A2, A3, B1, B2, C1, C2, C3
Now the actual number of servers is 7 and the number of databases is close to 1000. All servers are running SQL2005.
Is this something that Reporting Services is able to handle or do I need to look at some other solution?
Thanks,
Michael
View 5 Replies
View Related
Nov 5, 2007
Can multiple users connect to SQL Server 2005 concurrently?
View 4 Replies
View Related
May 30, 2006
Does anyone have links to various strategies for handling multi-user concurrency issues when updating data in SQL Server (2000 or 2005), either in the database (stored procedures) or via code.
I've seen a couple such as:
Check individual user updated columns to see if the database columns have changed and if not apply the updated columns (either individual updates or constructing a dynamic SQL statement).
Check all the fields for any change and if no change, update all the fields.
Check a version field for change and if no change, update all the fields.
What I haven't seen is complete solutions such as for the first one, ensuring the record is locked and can't be changed while checking for concurrency and ultimately updating the data. For the second one, how to raise an error if there was a problem, or for the third one how to ensure the record is locked between the version check and the update.
Even pieces of solutions are welcome (locking records, testing for change, etc). Just want to compare and contrast various methods as I create an infrastructure for 2005.
Thanks,
LarryC
View 2 Replies
View Related
Feb 7, 2008
Dear All,
i've altered my database to single user mode. and now trying to change to multi user mode with the query
alter database mydb set multi_user
but it is saying that it is in single user mode. query analyser closed.
how can i change that to multi user mode? idont know who opend that
to run sp_who it is not showing the database in dropdown list in enterprise manager.
thanks in advance
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 2 Replies
View Related
Apr 16, 2008
I've taken over development of a Vs2003(vB)/MsSql2000 application which usually runs in on a Windows 2000 server with multiple workstations running the application simultaneously in Remote Desktop Sessions.
The central user interface element of the application is an un-databound grid (actually a farpoint spreadsheet) which displays sql table rows that are accessable from every workstation. The grid refreshes its content whenever a specific subroutine is called. That subroutine performs a query which loads its results into a collection which is then loaded into the grid. The subroutine is called during startup and at other times using the rather elaborate mechanism described below. Each workstation also has the ability to modify any row in the grid via a dialog-based editor. The grid itself is not setup to allow inplace editing.
The grid usually contains about 200 records and 20-50% of these records get modified (some repeatedly) every hour.
My question involves the classic multi-user issues of keeping all of the seperate workstation's grids current as individual workstations make modifications to the data.
What is Microsoft's recommended method for handling multi-user data-refresh requirements in custom applications written in VB.NET2003 with MsSQL2000?
Now that you know what I'm looking for, let me add a little more detail. The existing code handles this multi-user refresh issue in the following way...
A SQL Trigger is attached to the SQL Table's Insert/Modify and Delete events. That Trigger calls the xp_logevent built-in stored procedure to make an entry in the NT Event Log.
The application contains a subroutine to handle EventLog("Application").EntryWritten messages. That subroutine calls the subroutine (mentioned earlier) which refreshes the grid contents through a new SQL query.
In this way, whenever any workstation changes the table data, a trigger fires which makes a log entry and broadcasts an EntryWritten message into the global windows environment. The individual applications (each running in their own RDP session) hear the message and respond by refreshing their grid.
This all seems a little "rube goldberg" to me, but it has worked for many years.
That is, until we moved the SQL2000 server to a seperate machine to improve program responsiveness.
Now when the trigger is fired and the log entry is made, the EntryWritten message is broadcast into the windows environment of the dedicated SQL server where there are no applications listening for it. Meanwhile, the applications continue to run with unrefreshed data on the (now) dedicated RDP server from where the EntryWritten messages cannot be "heard".
So my question really comes down to this...
In an environment where the SQL server and the RDP server are two seperate machines, what is the best method for coordinating multi-user screen refreshes across seperate applications running in distinct RDP Client sessions?
Thanks in advance for any assistance.
Peace,
Colt Taylor
Computer Golf Sofware
View 1 Replies
View Related
