Does any one have any idea of how to track a user access to a spesfic DB because we have an old DB and we don't know if any body using it, I know Profiler can help but I need to know when it was the last time this DB has been accessed.
I have a table in my database and it holds some important information,I want to track the users who are executing DML commands(select,update,delete) on the data in that table. Is there any way do that?If so how could i implement that.can any one help me in this regard? Thanks in advance...
Hello everyone, I have a fairly unique need :) I am trying todetermine the use/clients for databases in my corporation that I ammaintaining, but that noone seems to know what they are for. Many ofthese databases never seem to have anybody connected to them in thecurrent activity.What I'd like to do is find a way to audit the logins, so everytimesomeone connects to a database it simply logs the clients IP address,what login they used, and maybe what time. I've been searching googlefor this and have found tons of information on auditing the logins,but not the clients, such as by ip. Any help in this regard would beGREATLY appriciated!Joshua
Sybase and DB2 both have the capability of tracking user activities ata number of levels: invalid access attempts to databases, table, etc.;creation/deletion/modification of database objects/users/groups,grants/revokes.For MS SQLServer, the only setting that I've seen in the documentationis access attempts (none, fail only, etc.)The monitor program has the capability of tracking the events that Iwant to be monitored, but it seems as though these settings persistonly while the monitor program is running.I'd like these settings to persist permanently and the event records tobe sent to the system log.I can't seem to find the right term to get this information out of theMS Books On LIne.Help!
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?
I would like the Backup SQL user A can backup and restore DATABASE_a, what access right I need to grant to him. Although he can backup the database_a to the c:Program Files.....Database_a.BAK but he cannot restore it with some message said ''not enough security privieges..
Please quote some example right assigment so that I can replicate to our environment
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
Is there any way to get more information for when IAuthorizationExtension::CheckAccess fails to grant access to a report item for the current user? Specifically, it would be useful to know:
1. URL of attempted report 2. IP address of user agent 3. Identity of current user 4. Date/Time of the failed attempt
Is it possible to allow only a particular user to e.g. User1 to access the database the user created? Not even the Built-in administrator or the sa can access? I am using Mixed mode authenication..
My problem is that I can't query a couple of tables. I created them using DTS myself, in a db called contracts, and called one of them 010425. I've logged in as SA, and making sure the correct DB is shown in Query Analyser.
Here's the query...and very simple it is;
SELECT TOP 10 * FROM [dbo].[010425]
I just keep getting the following error message;
Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'dbo.010425'.
But it shows up in Sysobjects and EM!
I can query the system tables, using EXACTLY the same syntax...no problem there.
I've got SQL Server 7 running on W98. Everything is happening locally.
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
I need to be able to track which users and the number of times a user connects to a particular SQL Server database. I have a simple SQL Server database that users query (SELECT only) using an Access 2000 front-end. I do not have any control over the front-end so my only tools on the database side. I can't use 3rd party software, just SQL Server. Is there any way? Thanks.
Hi All, I need to give access to one user only to truncate a particular table. I am not able to frame exact query for this. However i can user EM and do it. But i wanted to know the query for this.
I know this seems like a strange ask, but we have a common user and pass in most of our web applications and this user requires DBO access to the database, no problem is occasioannly we need to let a developer look at an issue on a production server, so we port them through to sql through the firewall so they can see it it, and they normally login wih there domain/username and this way they are restricted to what they can see and do. However all developers know the SQL user and pass used in the web app, they also know its a DBO, so this means they can login with these user details and have access to everything.
My question is how can i lock this down so i can deny access from the SQL management console for this particular user, but still allow the user to act as a DBO for the web applications.
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?
We have an ASP.net application that currently sits on a server that runs IIS and sql server. We are spliting IIS and SQL server into 2 seperate machines. I believe I have the connection string okay as I can see the entries in the security log on the sql server machine however it keeps saying aspnet user invalid user or invalid password. What gives with this. We put .net on the sql server however this just added the aspnet user to the machine. Do I need to create an aspnet user inside the sql server and give it control of the dbases as well? I am running this using the personal web server not IIS on a server. I have a project on my desktop and am using the "local" IIS or personal web server that comes with visual studio out of the box. What kills me is that when I put the project on a real IIS server that has sql server on the same machine I have no issues. However when I split the dbase and the IIS apart onto 2 servers I get this aspnet invalid user or invalid password. HELP
Is there anyway, that I can generate a list of all the objects that a given user has access to (including type of access whether select or update etc), by running a SQL command? One of our databases has nearly 40,000 tables (no kidding!) I can always find this out by manually looking into the roles etc on the enterprise manager, but I need a way to find out using a T-SQL query..Thanks for any help!
I wonder if anyone can help. I'm trying to create a new user/login to a sql server 2000 box which will automatically have access to all the user databases on the instance. The user can't be a SA or anything however. It seems this can be done as the application which uses all these database has created such a user, however I need another which I will then make read-only.
Scenario: 1. Access to database objects is based on database roles. 2. Application administrators handle the assignment of users to database roles. Application administrators are first line managers, typically; they are not DBA's and not Help Desk staff.
Question: What products are available to simplify the management of user access to databases in the aforementioned scenario?
I need an example of how to grant access to a SQL user to a DB. For the life of me I can't seem to get my syntax correct. My database name is TEST and my username is LEMME_IN and I want to grant the user "Public" access to the db with db_datareader, db_datawriter database role membership.
I'm currently looking at a multi-user app that has an Access 97 frontend and an SQL2K backend. Proposed changes to the system mean that the user concurrency count is likely to increase dramatically, and I'm wanting to know if there are any limits to the concurrency for this architecture.
If a user is mapped to "master", (in login properties, user mapping) are they able to access all dbs, even though "master" is the only one with the check mark?
I have a DB creation script which starts with the following lines
EXEC sp_addrole 'RAVENROLE' go IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'USER1') CREATE USER [USER1] FOR LOGIN [USER1] WITH DEFAULT_SCHEMA=[USER1] go IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'USER1') EXEC sys.sp_executesql N'CREATE SCHEMA [USER1] AUTHORIZATION [USER1]' GO EXEC sp_addrolemember 'RAVENROLE', 'WS-NOLANKTFSBUILD2'
Basically what I am doing is creating a role, then adding user USER1 to it. The problem is USER1 has full update, insert etc. permissions on all tables in the database by default. Even when I deny that user permissions they can still update, insert etc.
I am using Windows Authentication, User1 also exists as a windows user.
How do I ensure that User1 has no permissions by default? I want to be able to explicitly add them.
With MS SQL 2000 Enterprise Manager, is there a way to allow a user accessto only a few tables, but deny the user access to the rest without having togo to all of the tables and denying access? The database has roughly 50tables, but only 3 should be granted to the new user, so as you can see itwould be a painstaking task to manually do this with the *cough* mouse. Or,if I can run some sort of grant script, that would work too. Thank you!