Auditing Users On Various SQL Servers

Jul 19, 2007

We have about 50 SQL Servers (most 2000 but some 2005) in different domains and behind firewalls and we implementing the PCI rules for the use of credit card data. One thing we need to do is to audit users and their rights. I have a stored proc that can run each night and record users and permissions.



My main problem is trying to bring all that data together on a central box and then parse through it to see if I have any offenders. The parsing is not the issue, but the polling is. I could use the SA account or create a audit account with SA rights but both of those solutions go against the PCI mantra.



Can someone who has done this give me some guidance as to what you did. I know that there are some third party tools that would probably do this, but my department is on a shallow budget and my overtime is free.



Any suggestions would be greatly appreciated. Thank you!



Jim Youmans

St. Louis

View 4 Replies


ADVERTISEMENT

Auditing On SQL Users...!

May 12, 2008



Hi,

we have database and sql login TESTUSER that all front end application (VB6) user as sql user to be able to communicate with DB. we have around 30+ users machine that is running that front end application.....!

how would i do auditing (who change what data and when, etc, etc) on each user action if needed. for that do i need 30+ unique users for each machine.

Thanks,

View 7 Replies View Related

Auditing SQL Server Users

Jul 28, 2004

I am trying to create a sql script that will check the database instance for any new objects that are created in any database on my system. These objects I want to audit are users, tables, databases, stored procs, etc.. I also want the script to email me and write the information to a text log file. Thanks in advance for any help.

View 2 Replies View Related

Sp_Login: Transferring Users/SIDs Between Servers

Jan 2, 2001

My question is, why did I have to use the allegedly optional named argument @DefLanguage when using sp_AddLogin? I had often tried to use the @SID parameter of sp_AddLogin to synchronize users betwixt our servers. Previous efforts had failed.

Per Transact-SQL Help, here’s the syntax for AddLogin:

sp_addlogin [@loginame =] 'login'
[,[@passwd =] 'password']
[,[@defdb =] 'database']
[,[@deflanguage =] 'language']
[,[@sid =] 'sid']
[,[@encryptopt =] 'encryption_option']

We have over a dozen User DBs, and I’d written a cursor to add the login and then GRANT access to the appropriate DBs. I used the following variables:

DECLARE @Loginame VarChar(25) , @Passwd VarChar(25) , @DefDB VarChar(50)
DECLARE @SID VarBinary(16)

Then I SET the variables:
SET @Loginame = 'MyUserName'
SET @Passwd = 'UsersPwd'
SET @DefDB = 'UsersDB'
SET @SID = 0x6370170BDAAFF640AD5CEB586EA87C2C
-- UserSID for MyUserName from sister server

Then I EXECuted the proc.
EXEC sp_AddLogin @Loginame , @Passwd , @DefDB , @SID

This generated the following error (instead of ‘?’, I saw squares, as in unprintable characters):

Server: Msg 15033, Level 16, State 1, Line 0
'????????' is not a valid official language name.

I didn’t understand this at all at first, but out of desparation, I DECLAREd & SET the @DefLanguage argument...And it worked! That is:

DECLARE @Loginame VarChar(25) , @Passwd VarChar(25) , @DefDB VarChar(50)
DECLARE @SID VarBinary(16)
DECLARE @DefLanguage SysName

Then added:
SET @DefLanguage = NULL
-- per sp_Configure, RunValue = 0
-- Master..SysLanguages reveals that language ID = 0 corresponds to ‘us_english’

Then I EXECuted:
EXEC sp_AddLogin @Loginame , @Passwd , @DefDB , @DefLanguage , @SID

Success!

Again, my question is, why did I have to use the allegedly optional named argument @DefLanguage?

View 1 Replies View Related

Moving Users And Permissions Between Servers--urgent

Nov 15, 2000

Is there any easy way to move users between SQL Servers for a particular database?
We have tests and development servers I would like to make sure the user lists are in sync for that db.

Thanks

View 1 Replies View Related

SQL 2012 :: Automate DB Users To Get Data For Every Month On All Servers

Aug 13, 2015

I need to automate the db users to get the data for every month on all servers ....

View 9 Replies View Related

How Do I Restrict Access To The Report Servers Header From Users

Jun 16, 2007

Hi

I'm looking to deploy some SQL Server reports and I want to restrict the access that the users have. Currently when connecting to the reports site they have access to a lot of functionality through the header bar, for example
- Properties
- New Folder
- New Data Source
- My Subscriptions
- Site Settings
- Search
etc.

How can I disbale or hide all these options so that all the user sees is the list of reports?

Thanks in advance

Mark

View 1 Replies View Related

Reporting Services :: Adding (new) Child Domain Users To SSRS As System Users?

Jul 28, 2015

We have an existing SSRS server, and have just created a new child domain. We'll be migrating users from the parent to the child, and want to add the users of that new domain with access to SSRS. In the parent domain they are able to access, but after migration with the child domain account, they cannot.

I have added the group CHILDDomain Users with a system user role on SSRS, and PARENTDomain Users was already there.

Is there any additional step I should/could take to get this active?

View 5 Replies View Related

Why Out Of The Blue Would VPN Users Be Unable To Connect To Database And Local Users Are Unaffected?

Mar 6, 2008

I have had this issue just pop up. I have local users who can connect fine, but my users that require connection by VPN cannot connect. I get the server not available or access denied error. I did confirm that the VPN'ers are connected to the network correctly and can see that their shares and mappings are correct. Any ideas? Thanking you all in advance!!

View 6 Replies View Related

SQL Security :: Did Not Create Any New Users And There Are No Other Users Listed In Accounts Section

Sep 28, 2015

I am trying to revert back to Windows 7 after upgrading to Windows 10, however it will not let me and the following message occurs: "Remove new accounts.Before you can go back to a previous version of Windows, you'll need to remove any user accounts you added after the most recent upgrade. The accounts need to be completely removed, including their profiles.You created one account (NT SERVICEMSSQLSERVER) Go to Settings> Accounts> Other users to remove these accounts and then try again".However I did not create any new users and there are no other users listed in the Accounts section.

View 2 Replies View Related

Stored Procedure To Get All Users Or All Users Of A Specific Country

Apr 30, 2008

hi alli've got two tables called "webusers" (id, name, fk_country) and "countries" (id, name)
at the meantime, i've a search-page where i can fill a form to search users. in the dropdown to select the country i included an option which is called "all countries".
now the problem is: how can i make a stored procedure that makes a restriction to the fk_country depending on the submitted fk_country parameter?it should be something like
SELECT * FROM webusers(if @fk_country > 0, which is the value for "all countries"){    WHERE fk_country = @fk_country}
who has an idea how to solve this problem?

View 9 Replies View Related

Auditing DB Changes

Sep 1, 2005

It seems to me there are two common strategies for doing DB audit trails via a trigger:

1. On an update to a row, duplicate that row in another table with
identical rows, except for perhaps the extra columns which represent
change date and changed by. Eg. When there is an update to the Customer
table, record the changes in Customer_Audit.

2. On an update to a row, check which fields were updated. Then in a
common audit table record the table, row ID, field, previous value and
new value of the field.

I'm wondering about the pros and cons of each. More specifically, do
the pros and cons change if you are using an O/R mapper (I'm using
NHibernate.)

Some thoughts on method 1 . It seems nice for an O/R mapper, since you
could have, say, a CustomerAudit Object inherit from your Customer
object and just add the properties change date and changed by. A
problem with this is you're going to add a whole lot of objects - one
for each object which you want to audit. Another drawback is that it
could be difficult to generate a history for a particular property
which was updated. Let's say I want to see the history of changes to
the customer's status. I have to load a collection of CustomerAudit
objects (which could be costly). Then I have to iterate through them
and compare the status properties to generate a history of statuses.
This is a pretty labor instensive method if you compare it to method 2,
where the change is recorded by field, not row.

Some thoughts on method 2. It's nice since the changes are by field,
not row, which (as above) makes generating a history easy. On the other
hand, you can never have a snapshot of a particular object at a
particular point in time. Moreover, I'm not sure how foreign keys would
be handled elegantly. I record that customer.statusID changes from 3 to
6. I'd have to do a seperate join to the customerstatus table to get
meaning for 3 and 6 (which method 1 would do automatically).

Thoughts? Any preferred way to do this with an O/R mapper?

Thanks

View 1 Replies View Related

Auditing

Mar 18, 2002

Hello,

I'm working on Sybase and recently started working on MS SQL Server. Can anyone guide me how to set up auditing for errors whether fatal or not?

Any help is appreciated

Thanks
Dinesh

View 1 Replies View Related

Auditing In SQL 7

Jun 29, 2000

A few days ago I saw a article on the internet (i don't know where), which described a way for auditing all database activities (like deleting records per user etc.) Unfortunally I can't find this document. Can anyone help me with this?

View 1 Replies View Related

Auditing DTS

Aug 18, 2003

how to audit DTS ?
80 % of DTS run from
exec master..xp_cmdshell 'dtsrun ....

20 % started by server agent

I have list with 200 DTS
I want to delete dts that are not in use.
Can I use profiler or it its possible to get info from system tables?

Thank you

View 1 Replies View Related

Auditing

Mar 18, 2002

Hello,

I'm working on Sybase and recently started working on MS SQL Server. Can anyone guide me how to set up auditing for errors whether fatal or not?

Any help is appreciated

Thanks
Dinesh

View 1 Replies View Related

Auditing

Aug 31, 2007

Guys,

We looking to audit tables in the database. In the tables auditing needs to be done only on few columns not all the columns. Is there any 3rd party tool available to do it can it auditing be done on columns only with sql server 2005. Trigger doesnt work in this scenario as it works per row it does not say which column in the row was updated/inserted/deleted.

For example in the below employee table, I want to audit(history) only ssn and dob columns - all the updates/inserts/deletes made to this column should be audited.

empidempname ssndob
1John,smith 122222222 07/29/2001

Any suggestions and inputs would help

Thanks

View 2 Replies View Related

Sql Report Works Fine On Internal Servers - Hosed On External Servers - Need Some Help

Nov 21, 2007

I have a report that was designed using SQL Reporting Services that sits on a SQL reporting server. It's nothing too exciting, it is essentially a three page application with legal jumbo on pages 2 and 3 and applicant data in fields on page 1.

We use rectangles to force page breaks to page 2 and to page 3.

When running the report on the report server, it shows and prints fine.

When running the report from the QA website internally, it shows and prints just fine.

When running the report from the production website from a machine internally, it shows and prints just fine.

When running the report from outside of the company network, the report is jacked. It obliterates large chunks of text, crams text together, and creates blank pages.

I need help in determining where I even begin with trouble shooting this!

View 1 Replies View Related

Auditing A Table

Sep 19, 2001

I have a table and the data in this table (for no rhyme or reason)is being deleted somehow. I'm looking for suggestions on how to audit this table and find out who or what process could be deleting my data.

View 3 Replies View Related

Auditing On Sql Server

Jun 14, 2000

Hi,
i need to set up some security standands in sql 6.5/7 . These includes auditing login attempts success and failure, access to db objects etc. I know sql is has very limited capabilities . can anyone tell me how to implement this without using event viewer etc??

View 2 Replies View Related

MSSQL 6.5 Auditing

Oct 20, 1999

Hi folks. I am a new DBA and working on taking over several existing MSSQL 6.5 applications.

My company needs to be able to audit the following actions on MSSQL.

login
logout
insert into specific tables
update in specific tables
delete from specific tables

I am looking for a 3rd party product that provides these services for SQL 6.5. I have found Braintree products and they have really nice auditing but for Oracle and not MSSQL. I have also looking into using triggers to build an audit log for me but the applications we are using require periodic updates that would force me to rebuild the triggers. This may cause too much administrativia.

Any Ideas?

Thanks, Aaron

View 2 Replies View Related

MS SQL Server Auditing

Nov 14, 2006

I want to know are there any other third party tools that are used for Auditing the SQL Server Like...DBAudit.

View 2 Replies View Related

Server Auditing

Sep 23, 1998

Hi all:

I need some help with this. I have a development server and all the developers log in as sa. Lately test data has been mysteriously deleted from selected tables. I need to track the time that the activity is taking place so I can figure out who might be playing this little game. Can anyone suggest what I can do to find this out? Your quick response will be appreciated.

View 3 Replies View Related

Auditing Sa Activities

Jun 24, 1999

Someone had changed the SA password on one of my servers. I need to find out who did this. Can you tell me if there is any historical information kept on any of the system tables that can tell me who (what machine name) and when (date and time)this was done?
Does anyone have a 3rd party or inhouse developed task/procedure to report this kind of security issues?

View 1 Replies View Related

Auditing Trigger

Mar 20, 2006

hey all, i found this auditing trigger, currently it just kicks out what was changed and when, id like to add who cause the trigger to fire as well (currently its just set to the "inventory" table). check under the "add the audit fields" comment for info:

CREATE TRIGGER TRG_inventory
ON [DBO].[inventory]
FOR DELETE,INSERT,UPDATE
AS

DECLARE @ACT CHAR(6)
DECLARE @DEL BIT
DECLARE @INS BIT
DECLARE @SQLSTRING VARCHAR(2000)

SET @DEL = 0
SET @INS = 0

IF EXISTS (SELECT TOP 1 1 FROM DELETED) SET @DEL=1
IF EXISTS (SELECT TOP 1 1 FROM INSERTED) SET @INS = 1

IF @INS = 1 AND @DEL = 1 SET @ACT = 'UPDATE'
IF @INS = 1 AND @DEL = 0 SET @ACT = 'INSERT'
IF @DEL = 1 AND @INS = 0 SET @ACT = 'DELETE'

IF @INS = 0 AND @DEL = 0 RETURN

IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[AUDIT_inventory]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
BEGIN
-- CREATE A MEMORY TABLE CONTAINING THE FIELDS AND TYPES OF THE TABLE
DECLARE @MEMTABLE TABLE
(
ID INT IDENTITY
,COLUMNAME SYSNAME
,TYPENAME VARCHAR(20)
)
-- INSERT THE COLUMNAMES AND THE DATATYPES
INSERT @MEMTABLE
(COLUMNAME,TYPENAME)
SELECT NAME,TYPE_NAME(XTYPE)
FROM SYSCOLUMNS
WHERE ID = OBJECT_ID('[DBO].[inventory]')
ORDER BY COLID

DECLARE @CUR INTEGER
DECLARE @MAX INTEGER
DECLARE @SQLSTR AS VARCHAR(8000)
DECLARE @CURCOL SYSNAME
DECLARE @COLTYPE AS VARCHAR(10)

-- SETUP VARIABLES
SET @SQLSTR = ''
SET @CUR=1
SELECT @MAX = MAX(ID) FROM @MEMTABLE

-- LOOP EVEY FIELD
WHILE @CUR <= @MAX
BEGIN

-- GET VALUES FROM THE MEMTABLE
SELECT @CURCOL = COLUMNAME,@COLTYPE = TYPENAME FROM @MEMTABLE WHERE ID = @CUR
IF @COLTYPE = 'INT' OR @COLTYPE = 'BIGINT' OR @COLTYPE='UNIQUEIDENTIFIER'

-- WE DO WANT TO COPY INT/BIGINT/UNIQUEIDENTIFIER FIELDS BUT IF THEY ARE AN
-- IDENTITY OR A ROWGUIDCOLUMN WE DO NOT WANT TO COPY THOSE ATTRIBUTES

SET @SQLSTR = @SQLSTR + ' CAST('+@CURCOL + ' AS '+@COLTYPE+') AS [' + @CURCOL +'] '
ELSE

-- ANOTHER FIELD DO NOTHING JUST COPY IT AS IT IS
SET @SQLSTR = @SQLSTR + ' '+@CURCOL + ' AS [' + @CURCOL +'] '
IF @CUR <= @MAX - 1 SET @SQLSTR=@SQLSTR + ','
SET @CUR = @CUR + 1
END

-- ADD THE AUDIT FIELDS
SET @SQLSTR = @SQLSTR +',CAST('' '' AS CHAR(6)) AS TRG_ACTION,CAST(GETDATE() AS DATETIME) AS TRG_DATE ' - tryin to add who made the update here, figure out what GETUSER translates to

-- SET UP THE SELECT FOR CREATING THE AUDIT TABLE
SET @SQLSTR = 'SELECT TOP 0 ' + @SQLSTR + ' INTO [DBO].[AUDIT_inventory] FROM [DBO].[inventory]'
EXEC(@SQLSTR)
END

IF @ACT = 'INSERT' INSERT [DBO].[AUDIT_inventory] SELECT *,'INSERT' ,GETDATE() FROM INSERTED
IF @ACT = 'DELETE' INSERT [DBO].[AUDIT_inventory] SELECT *,'DELETE' ,GETDATE() FROM DELETED
IF @ACT = 'UPDATE' INSERT [DBO].[AUDIT_inventory] SELECT *,'UPDATE' ,GETDATE() FROM INSERTED

View 10 Replies View Related

Transaction Log Auditing

Jul 20, 2005

Is there a tool that can report transaction log information? Such asreporting what tables/columns were updated, by whom and when,etc....Thanx.

View 1 Replies View Related

Best Way Of Auditing Data

Oct 10, 2007



Hello
I am more of a reporting person, recently I was asked to create stored procedure for an upcoming ASP.NET
application.
We have a problem that we are facing and any suggestion would be very helpfull.

The problem is that we have like 8 differtent tables each with 10 to 15 columns in it.
The front end application has pages with save , update , delete button which are insert,update,delete
for each of the 8 tables
i.e they save , update , delete these 8 tables

They want to a way to Update , this audit table which stores information
like ---

Date
User
Table
Column name
Previous column name
new column name

So for each row that was updated in those 8 tables , each column will have the above fields updated as a row of data

Initially we thought about triggers but it will be like 60 triggers ...Is there a better or other way of handling this?



Any help is appreciated
Thanks

View 3 Replies View Related

Auditing Best Practise

Apr 30, 2008

I want to log all changes made to a table (only updates, since there will be no deletes or inserts).

I would like to see the user who changed it, the date and time, fieldname, old value, new value.
If more fields are changed during the update, than add more records into the logging table.


What is best practise to achieve this?


Thanks,
Frans

View 13 Replies View Related

Auditing Trigger

Apr 17, 2008

Hi


I would like to create a simple trigger on a Customer table to fullfill two fields, on should be filled when a new customer is inserted (DateAdded) and the other when the customer is edited (DateEdited), Yes I know it is quite simple but as I am not a database expert I do not know how to solve this problem.


Thank you in advance.

View 11 Replies View Related

C2 Auditing Documentation

Sep 20, 2007

Hi everyone,

I am to try to find documentation about C2 auditing into SQL Server 2005. The only one doc I fand about it is:
SQL 2000 C2 Admin and User Guide
It cover SQL Server 2000 and Windows NT4.0.

I do not find any document who cover the security from SQL Server 2005.

Can you help me please?

Thx.

Laurent

View 1 Replies View Related

Sql Server 2k Auditing

Oct 20, 2007

I'm wanting to do some auditing with sql 2k and wish to get the users first and last name of the windows account to log into a table. You can easily access the windows account name by using the System_User keyword. However, is it possible to get the first and last name of the system_user? If so, how?

Thanks.

View 10 Replies View Related

SSIS Auditing Example

Dec 14, 2006

Greetings SSIS friends,



Do any of you guys have a link to a website with an example of how to create a solid audit system for SSIS packages?



Thanks for your suggestions in advance.

View 14 Replies View Related

Auditing Data Changes

Jun 14, 2007

I am using the SCD Wizard and it is working nicely. Can someone point be to an article/tuorial that would explain how you could create an "audit trail" on the items that may have been changed (type I and II)?



Basically, what I want to be able to do is run a query that tells me what data may have changed. I figured I would have to create an auditkey field in my table which would then link the key to the change detail?

View 7 Replies View Related







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