We are looking to build a SQL Server database that will be hooked up to
a Web server (WebLogic or a .NET).
The database will house data for multiple customers, and the
requirement is to have no customer see other customer data. Web server
will be responsible for authenticating users (ids and passwords will be
maintained there/possibly stored in the database, but will not be
actual database logins) -- and establishing pooled connections to the
database, using some sort of a proxy login.
Ideally, we do not want to have the database itself visible to "the
world", it should reside behind a firewall, and be accessible through
the Web server, which will do all authentication and routing.
We want to have either tables or views defined in such a way that:
1. Users can only see their own data and NEVER can see anybody else's.
2. Users' access to the database is logged (this includes updates as
well as queries).
3. We would like to provide some sort of a reporting mechanism -- where
users can form their own queries. So, something like a Crystal or an
Access front end, that users are typically familiar with... The problem
is that this requires users having direct access to the database (?),
and opens up tables (unless, there is a way to create parameterized
views that will always restrict access to a subset of records).
Any advice on how to best implement this is greatly appreciated.
Let me start off by saying I'm not a SQL expert and have very limited knowledge on the topic.Here is my question:Our organization has an archiving solution that stores data in a SQL database. This applicaton creates two SQL accounts. One that is used to archive to the database. The second to browse the database from a search interface provided by the application. With all the federal compliance issues I see that they require the data to be stored in "a non-rewriteable and non-erasable format". My question is. How can I prevent the database from being erased in the SQL database? As 'Administrator' I can open Enterprise Manager and open a table in the database and simply delete any record that I wish. I understand that might have this ability because I'm logged on as Administrator. How do I prevent access to this database and prevent access modify the records. I believe the only two accounts that should have access to the database are the Archive and Browse accounts required by the application.
Ok, I need to give some details. I have a secure Access Database using WorkGroup Security logon. It is an Access 97 database (yes I know it is "old").
I tried to create a DTS package in SQL Server 2000 to connect to it, but I am getting the same error that I got before I "joined" the workgroup when attempting to access the DB. So I changed the Service logon identites to mimic <me> as the logon, and still I can't get it to connect. The user name and password I put in the connection are correct.
It seems like nothing is working and I am all out of ideas. Does anyone have any recommendations? Has anyone done anything like this successfully?
PS: I can manually logon to the database, and extract the data 1 table at a time, but I don't want to export 40 tables each time I have to load this data. Any help would be appreciated. Thanks in advance.
SQL Server 2000 DTS Package Need to access a secure website which displays a list of available files for download. Firstly need to read that page to determine the most recent file. There is a view link to the file which uses java script to post back information for file download.
I'm writing a C# application which connects to a local SQL database for data access. The application connects to SQL Server through windows authentication, but opens up the port and sqlbrowser to others on the network wanting to access the database through SQL Server authentication, and also allows remote users to connect to this server remotely if they have the login and password (and because the port is already open)
I understand this is not secure and open to attack, and am unsure of how to secure these processes without blocking these three types of access, from A.) the local user, B.) the network user and C.) the remote user across the net.
Have researched this a fair bit, but get somewhat lost amongst all the jargon.
Not sure if I'm posting to the right group or not, pls tell me where else to post.
My question is: I have a microsoft access database on a client desktop that links to tables in sql server. Is there a way to encrypt the data that goes back and forth between access and sql?
We have been asked whether it is possible to completely delete sensitive data. When data is deleted it is not actually completely removed from the database but is marked as deleted. It is possible to zero out that data using sp_clean_db_free_space but this doesn't affect the transaction log.
We had hoped that CHECKPOINT would clear the data from the log but we are not completely certain of this. So the question becomes is there a built-in command or function in SQL Server 2008, or subsequent versions, that will completely remove deleted data, both from the transaction log and the database itself? Or is there a 3rd party tool to accomplish the same thing?
I have read recommendations about searching encrypted data. Typically, they involve creating a MAC (message authentication code) table. One of the elements of that table is a HASH of the encrypted data (plus a Mac key) that is used as an index for searching. Is that HASH as secure as the encrypted data itself, or is this approach less secure? If it is less secure, then may I assume that approach is the only feasible way to search data encrypted by nondeterministic algorithms?
I'm trying to re-write my database to de-couple the interface (MS Access) from the SQL Backend. As a result, I'm going to write a number of Stored Procedures to replace the MS Access code. My first attempt worked on a small sample, however, trying to move this on to a real table hasn't worked (I've amended the SP and code to try and get it to work on 2 fields, rather than the full 20 plus).It works in SQL Management console (supply a Client ID, it returns all the client details), but does not return anything (recordset closed) when trying to access via VBA code.The Stored procedure is:-
USE [VMSProd] GO /****** Object: StoredProcedure [Clients].[vms_Get_Specified_Client] Script Date: 22/09/2015 16:29:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
Hi. I have a SQL statement currently ending with "ORDER BY sc.TypeID, sc.ObjetID DESC"where the TypeID can be of value 1,2,3,4,5,6,7,8, 9 and 10. What I would like to achieve is to selectively order the datas by TypeID, meaning in this case that I want ALL the TypeID of value "1" to come first and then all the rest ("2" and higher) un-ordered. This is important that the rest remains unordered by their TypeID as I only want them to be ordered by their ID (ObjetID DESC). Is that possible? If yes how?Thank you.Francis
Hello all, Is there a way to do a selective restore? I need to create a dev db and the prod backup is 17 gig and I have a space crunch on the server. My idea was to restore the complete backup and subsequently shrink the db size after truncating data in tables which are not frequently used? Is that a good idea. Any help will be appreciated.
I need to be able to export from an existing database, all fields with a certain column id. I have been pointed in the direction of bcp, however I am having difficulty finding the right syntax.
Is it possible to use replication for inserts and updates only for some tables and inserts updates and deletes on others. I want to be able to delete data from the source database but not my replicated database. Does anyone know of a way to use replication to facilite this?
I'm in the current mode of migration from WIn2k and I 'm trying to setup user where they are only allowed access to one table.
If I grant thenm read /write access to the db they can obviously see all of the db due to the size of the db I do not wish to go down the root of denying every table as there is over 100. Is there an easier way??
I was trying to use the case statement in where clause so that the comparison (> , <, =) can be made depending upon the value of the local variable @fl.
PLease tell me how to do that. Or suggest me how can I make selective comparison (<,>,=) in where clause depending upon a local variable. Please note that I donot want to use if/else satement and write select statement everytime based on the local variable as in that case the code length will be huge. Please suggest me a coensized query.
SQL that I queried:
use pubs go
declare @fl int
select fl = 3
select * from titles where case fl when 1 then price > 90 when 2 then price < 90 when 3 then price = 90 end
The following error is given
Server: Msg 170, Level 15, State 1, Line 7 Line 7: Incorrect syntax near '>'.
HI, Is it possible in SQL Server to restore Databases partially? In the sense, I want to backup just 2 tables from a database & restore them, instead of having to take a backup of the entire database & restore the entire database.
One way to do would be to export data & then importing data for only those selective tables, but I dont want to do that, bcause that would probably take more time & can be error prone.
I need to 'copy' selective (filtered) rows from table 'A' to table 'B' on a regular basis, but only if they do not exist in table 'B'. If rows get deleted from table 'A' they must remain table 'B'. If rows change in table 'A' (unlikely) they should be update in table 'B'
What feature of SQL should I be using? - triggerering a stored procedure on Insert - replication - SSIS
I am using Visual Studio 2005 professional and SQL Server 2005 Express Edition.
I am having a bit of an issue at the moment with my project. I have a large SQL server database (over 600,000 rows of data decoded from a text datafile and stored by my C# program) where I need to select a series of distinct rows.
I have a datacolumn of varchar(10) called UID which is assigned to every row in my database. It's not a unique code so it can happen several times. By selecting a specific UID code, I can narrow down my selection to a specific range of rows (which are incremental in order). This range will contain many names.
What I want to do is find a UID which will have one of two names. I have so far done this:
SELECT DISTINCT F1.UID FROM MyDataBase F1 INNER JOIN MyDataBase F2 ON F1.LineIndex < F2.LineIndex AND F1.UID = F2.UID AND F1.Names IN (Bob', 'John', 'Peter', 'Sarah', 'Anne', 'Stewart', 'David', 'Alan', 'Linda') AND F2.Names IN ('Bob', 'John', 'Peter', 'Sarah', 'Anne', 'Stewart', 'David', 'Alan', 'Linda')
This will return any UID which contains at least two of these names. The LineIndex ensures that the comparison doesn't happen on the same row of data (it's a column set to type int with IDENTITY(1,1) which is also my PRIMARY KEY). This is partially what I want, the selection of UID rows which will have at least two names from the list. Also, the same name will never appear twice in the same UID list.
Now, the problem I am facing is that I want a select a UID range with names in the order I have specified, so if I want a UID list where the names I want are Peter and Linda, I only want to return a set of UID rows where Peter appears earlier in the returned rows than Linda.
It is possible that other UIDs have the same names, but in reverse order, starting Linda, Alan, David etc...John, Bob. I would want to discard these entries.
Can anyone please give me any hints as to how I could get around this?
I've got a report linking to a SSAS cube. It has 2 row groups Region and Country. The Country group is a subset of the Region group and works fine, it collapses and expands ok.
Now, due to a boss who doesn't like the way this looks, I have to find another way to display it. I was wondering if it is possible to have a report parameter that could be selected and the appropriate column grouping be displayed depending on the value.
i.e. User selects Report Parameter value 'Region' and the Region row group becomes visible, the Country group becomes invisible. The exact opposite happening if the user selects Country.
I can't find any obvious way to do this. Any ideas?
I am trying to do selective updates for rows where a column matches a column in another table. I want to do something like this, only 'this' does not work, and nothing else I could think of (I tried joins also) worked. What am I missing? I hope this explanation makes sense.
UPDATE queryresultsmodel SET queryresultsmodel.tableforcedoutdate = getdate() Where Exists (Select tablename from queryresultsmodel q inner join orphanul o on q.tablename = o.name)
We have a Table that contains a schedule. The schedule has certain pieces of information that are required to be updated by certain members of the organization, specifically Shift, Start Date, and Line. Each record also has a column for the customer that it is for.
The schedule has a Column, [CSR] which lists the name of the person who is responsible for updating it. Format of that is domainnamejohndoe
What we would like to do is block domainnamejohndoe from being able to update or insert on any of the records that he is not responsible for.
So if he attempts to change the shift for example, it denies the change and possible pops up an error indicating that the change was blocked because he is not the responsible party.
Input in this is through a query which views the table data in MS Access. It cannot be moved to a Form without some serious redesign, so I cannot put the block in there. I assumed that this could be blocked with a Trigger that checks the [CSR] column, matches it to the logged in user through the suser_sname(suser_sid()) and then either allows or denies the update.
So is this a viable idea, or should I explore the move to an input form and make the change there in MS Access via VBA.
At first we started with just an audit trail, but I started to think that the audit trail would be needed only for records that should be allowed to be changed and that we should deny changes to those who are not allowed to make them.
Below is the audit trail trigger, I figure that the change should occur somewhere within that first IF statement, or make the first IF the second, and the first should be the permission check.
At this point I am stuck as to how to block the updates. I tried using Deny but that was not working. Filtering the results by only showing the logged in user their records is not an option as they need visibility to the entire schedule.
This is for SQL 2000 SP4/Windows 2000 SP4
CREATE TRIGGER audit_mschange ON dbo.T_PP_Table_2 FOR update AS IF (UPDATE([Start Dt]) OR Update(shift) or Update (comments) or Update(status) or Update (line)) BEGIN
SELECT ins.[PP Ord No], del.line,ins.line, del.shift,ins.shift, del.comments,ins.comments, del.status, ins.status, getdate(), suser_sname(suser_sid()), del.[Start Dt], ins.[Start Dt] FROM inserted ins, deleted del WHERE ins.[PP Ord No]=del.[PP Ord No] AND ins.autonumber=del.autonumber AND (ins.status <> del.status OR ins.[Start Dt] <> del.[Start Dt] or ins.status <> del.status or ins.comments <> del.comments or ins.line <> del.line ) END
I have to write an SQL Server 2000 stored procedure that returns rows from a table (a SELECT with an ORDER BY). A front end system calls the stored procedure and displays the returned rows. If there is more than one screen's worth of data (ie more than 20 rows returned from the table) then there is a requirement that the stored procedure only returns the rows for the screen ie, for screen 1 I need to return rows 1-20, for screen2 I need to return rows 21-40, screen 3 = rows 41-60.
The screen number will be passed into the stored procedure so I can work out what rows to return, the only problem is how can I tell SQL Server to only select the required rows (ie, say rows 21 to 40 from the returned rows). I can't see any arguments that allow selective rows to be returned (apart from TOP).
I am beginning design for a project to maintain an on-line archive of historical data for a production database, and then truncate old, rarely-used data from the production database. I am considering a method whereby we (A) replicate data changes from the production database to an archive database and (B) truncate old data from the production database according to predetermined business rules.
I am trying to determine whether SQL Server merge or transactional replication can be used to accomplish the replication portion of this requirement.
Given this scenario, there are two types of modifications that are made to the production database: (1) application inserts, deletes and updates which should be replicated, and (2) truncation deletes (as described in "B", above) which should not be replicated.
My question is: Will I be able to control the replication process such that application deletes are replicated, but "truncation" deletes are not? Could static row filters somehow be used to accomplish this?
I have been banging my head against a brick wall with this for a couple of days now. I am wrighting a custom front end for one of our SQLServer DB's and need to run a select agains the DateTime Field. The field Format is [DD/MM/YYYY HH:MM:SS]. I am tring to get the select to pull all the records on a set date only (keeping the time available but not selecting against it) so that the brass can see at what time on a given day an action was performed. Problem is, the way the programing software works, I can only build the SQL using string information. I have tried CONVERT and CAST both ways (converting the date to varchar and converting the string to date and also converting them both at the same time). Everything that I have tried returns an empty data set when used with the WHERE LIKE criteria (oh yeah, I'm also using LIKE % wildecards to retrive all the times against a specific date) but does populate when no filter is applied.
This is where my SQL is at just now:
SELECT Files.FileID, Useres.UserName, FileStatusLog.StatusCode, FileStatusLog.StatusDate FROM (Files INNER JOIN FileStatusLog ON Files.FileID = FileStatusLog.FileID) INNER JOIN Users ON FileStatusLog.UserID = Users.UserID WHERE (((CONVERT(varchar(10), FileStatusLog.StatusDate,103) LIKE '%{variable}%'));
This also returns an emtpy data set when the LIKE is replaced with ='{exact date entry in format: DD/MM/YYYY}', but does return the full table when the WHERE is removed and also when the LIKE is set to just a bare '%' with no other filter values.
Because of the fact that the results are returned when LIKE '%' is used I am not even sure if it is the CONVERT that is causing the problem. I have even tried assigning the escape character manualy to '+' incase the / values of the date in the LIKE variable where escaping other characters. Needless to say this did not make life any better.
As you may have noticed already SQL is not exactly my strong suit, but no one else here even knows what it is, never mind what it's used for, so I got droped with the task. Also - The DB was created by a third party so I have no write permissions to the DB.
Any help with this would be greatly appreciated as it's gotten personal now
I have a client who has SSMS installed on her laptop. She is able to connect to the SQL server via SSMS in the office and query data on the server.
She needs to be out of site often and doesn't have internet access. She asks if the data tables can be "backed up" or saved on her laptop, so she can look at them without worrying connecting to the server. I am not sure if this can be achieved, as SSMS is built for accessing a server, not a desktop. Myself never have this need. If I really need it, I would go to Microsoft Access and create an ODBC connection to the datatables. But this client thinks that Microsoft Access is beneath her.
I have recently upgraded to SQL2014 on Win2012. The Access front end program works fine.
But, previously created Excel reports with built in MS Queries now fail with the above error for users with MS 2013. The queries still work for users still using MS 2007.
I also cannot create any new queries and get the same error message. If I log on as myself on the domain to another PC with 2007 installed it works fine, so I don't think it is anything to do with AD groups or permissions.
We need to insert data/rows from a SQL Server 2014 database into MS Access database. The problem is, there are so many columns (100+) in the table and there are so many insert transactions of this kind (from different tables) that it is not very easy to write the code in VB.NET that lists all column names.
Both the Access and SQL Server tables have the same number of columns and the equivalent data types, so inserting is not really the problem. It's just that is there a way to do an insert statement in T-SQL that does not name all the columns?
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
