Monday, January 14, 2013

Should a DBA have SA rights all the time?

I was having a look around and I have not seen many questions or Answers on this topic.
I did come across a post from Ted Krueger and also Brian Kelley on Should DBAs have local admin rights, this question above is a little different but I guess should cover both Local Admin and SA rights to the SQL server.

I have worked as a SQL Server DBA for many years working for the UK government to Banking. In all these years I have seen so many changes to the DBA job, when I first started out I was a Network Engineer, DBA and SA Slowly this has changed to being a glorified F5 pusher.

Should a DBA have SA rights all the time?
DBAs would almost defiantly argue yes as it makes their jobs easier. It defiantly does, no the other hand users, security risk and other admins may argue that it will make there jobs harder to monitor this.

As a DBA I am now in the thought on how we can manage risk and I think there is no right or wrong answer to the above question but now I am leaning towards the Risk and Security aspects of a DBA and the systems they could be in charge of, If you work in banking should you have full control of a database which may hold thousands of user account numbers and balances. You may also argue that the finance crisis of late was never caused by a DBA stealing the money it was the traders. but yet again would you want the blame, I once did a migration as a DBA in a large intuition that involved 100 billion pounds being shipped to Hong Kong from London, the money went missing from he database and ended up in another bank who was the first person they came to me. Not the person who sent the money to the wrong location they asked do you have a large amount in your bank account, I said now but do you really think I would be here if I did sarcastily as they never even knew the money was not in there account for 3 days. I did manage to track down where the money had gone through the database and they found the right person.

Need less to say taking away a DBAs rights and then asking them to elevate on a task will never stop the above scenario happening. But it can be controlled, through an elevation procedure and audit scripts that audit what everyone does.
This would only really work in a Medium to Large company you could never impose such large restrictions on a small company usually were the DBA is the SA and so on.

How can we monitor what users are doing?
Audit scripts, DML, DDL audits
Check out the post here for table audit scripts, I will post one for database audit and changes a little later.

I will work on a script for elevating a user to SA this way you only need to rights to do your day to day jobs, IE backup check jobs failed, run sp_who2 active and so on enough to diag performance issues and so on without seeing customer data.

on to local admin rights I also do not think a DBA should have these, if a drive feels up then the windows admin should ask the user or DBA which files to delete, I think logging on to a server could be a bad thing, I once knew a DBA who logged into a server and restarted it and also deleted files that he should not have done as they were on the same drive as the MDF, and the database was full. This is better for the apps team to do or the windows admin.

However, again one may argue that this makes the DBAs job easier and that the DBA should be trained, however, I go for the segregation of duty aspects and let others do a job they are meant to do and leave us to do our job.

To conclude on what I am saying here, is that we should not lock everyone down; it should just depend on your organization. It should depend on the data in question. It should depend on the controls you have in place. I will also try to add some above that you may like to implement, IE audit and elevation.
Even if you’re a small company it is good to add audits I once worked in a small company where someone run out of space and did not know that the master, msdb and so on were for starting MSSQL and deleted them. Needless to say the server was down a while having an audit would have shown who did this obviously the person who did it deninded it and it was hard to prove.
You should always look at every situation in these cases too and choose the best practices you should also look at what type of DBA you have dev or prod, dev DBAs may not be able to access prod servers you may need some sort of application support that works with a DBA on this you may not its up to the ORG HR and so on.
You may have HR Data and intellectual property that you don’t want people to see and it makes sense to lock these down to the DBA. Nevertheless you may come to the conclusion that the DBA needs these rights anyway, that would be fine if its what your org needs and there should be nothing wrong I would just suggest to look at the question and consider it. 

Thanks for your time please check back in a few days for audit scripts. 








Thursday, July 12, 2012

audit storeproc

Here is a good storeproc to audit your database tables for changes

first create table for your audit list

****** Object: Table [dbo].[Auditlist] Script Date: 07/12/2012 15:10:35 ******/ ANSI_NULLS ON QUOTED_IDENTIFIER ON ANSI_PADDING ON TABLE [dbo].[Auditlist]([sz30tablename] [varchar]
)
(128) NOT NULL ON [PRIMARY]GO
SET
GO
ANSI_PADDING OFF

then insert into this the tables you want to audit

insert into auditlist values ('tablename')

then create the proc

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_addaudittable]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_addaudittable]
GO
USE [databasename]
GO
/****** Object:  StoredProcedure [dbo].[sp_addaudittable]    Script Date: 07/12/2012 14:46:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create proc [dbo].[sp_addaudittable] @tablename varchar(40)
as
declare @databasename varchar(30)
declare @status  smallint
declare @statement varchar(8000)
declare @id  int
declare @audittable char(50)
declare @identity  tinyint
set nocount on
 if not exists(select name from sysobjects where name = 'Auditlist' and type = 'U')
 begin
  print '--Auditlist table does not exist'
  return 1
 end
 if not exists(select name from sysobjects where name = @tablename and type = 'U' and uid = 1)
 begin
  print '--Source table does not exist'
  return 1
 end
 if not exists(select sz30tablename from dbo.auditlist where sz30tablename = @tablename)
  insert into dbo.auditlist (sz30tablename) values (@tablename)
 if exists(select name from sysobjects where name like @tablename + '_a' and type = 'U' and uid = 1)
 begin
  print '--Audit table already exists - please drop first'
  return 1
 end
 if exists(select name from sysobjects where name like @tablename + '_a' and type = 'U' and uid <> 1)
 begin
  print '--Invalid audit table. Not dbo.'
  return 1
 end
 SELECT @databasename = db_name(),@audittable = @tablename+'_a'
 SELECT  @status = (status & 4)
 FROM master..sysdatabases
 WHERE name = db_name()
 if @status = 0
 begin
  exec sp_dboption @databasename, 'select into/bulkcopy',TRUE
 end
 if exists(
 SELECT  *
 FROM  syscolumns
 WHERE  (status & 128) = 128 and
  object_name(id) = @tablename )
  SET @identity = 1
 else
  SET @identity = 0
 if @identity = 0
  SET @statement = 'select * into ' + 'dbo.' + @tablename + '_a from ' + @tablename + ' where 1>2'
 else
  SET @statement = 'select t1.* into ' + 'dbo.' + @tablename + '_a from ' + @tablename + ' t1 join ' + @tablename + ' t2 on t1.identitycol = t2.identitycol where 1>2'
 execute (@statement)
 SELECT @statement = 'alter table dbo.'+ @tablename + '_a ' + 'add sz30auditusername varchar(30) null, sz1auditType char(1) null, sz30audithostname char(30) null, dtaudittimestamp datetime null'
 execute (@statement)
 -- Now we need to get rid of the timestamp column
 declare @TimestampColname varchar(100)

 SELECT @TimestampColname = syscolumns.name
 FROM  syscolumns
 JOIN  systypes on syscolumns.xusertype = systypes.xusertype
 WHERE  systypes.name like 'Timestamp' and id = object_id(@tablename)

 if @@rowcount > 0
 begin
  SET @statement = 'alter table dbo.' + @tablename + '_a drop column ' + @TimestampColname
  exec(@statement)
 end
 -- Now get a list of columns
 declare @cols table (colid int, colname varchar(100))
 INSERT
 INTO @cols
 SELECT  colid,
  syscolumns.name
 FROM  syscolumns
 JOIN  systypes on syscolumns.xusertype = systypes.xusertype
 WHERE  systypes.name not like 'timestamp' and id = object_id(@tablename)
 declare @colname varchar(100)
 declare @colnames varchar(8000)
 declare @first tinyint
 SET @colnames = ''
 SET @first = 1
 while (1>0)
 begin
  SELECT  @colname = colname
  FROM @cols
  WHERE colid = (SELECT min(colid)
    FROM @cols)
  if @@rowcount = 0
  begin
   break
  end
  else
  begin
   if @first = 1
   begin
    SET @first = 0
   end
   else
   begin
    SET @colnames = @colnames + ','
   end
  end
  SET @colnames = @colnames + '[' + @colname+']'
  DELETE
  FROM @cols
  WHERE colname = @colname
 end
 declare @addcols varchar(1000)
 SET @addcols = ',[sz30auditusername],[sz1auditType],[sz30audithostname],[dtaudittimestamp]'
 select @id = deltrig from sysobjects where name = @tablename and type = 'U'
 if @id = 0
 begin
  print '--Warning this table has no delete trigger - Printing please run'
  SELECT @statement = 'Create trigger tr' + @tablename + 'Delete on ' + @tablename + ' for delete as if exists(select sz30tablename from auditlist where sz30tablename=''' + @tablename + ''') insert into ' + @tablename + '_a (' + @colnames+ @addcols + ') select ' + @colnames +',user_name(),''D'',host_name(),getdate() from deleted'
  print @statement
  print 'go'
 end
 else
 begin
  if not exists(select * from sysdepends where depid = object_id('auditlist') and id = @id)
   print '--Warning this table''s delete trigger does not access audit log'
  if not exists(select * from sysdepends where depid = object_id(@audittable) and id = @id)
   print '--Warning this table''s delete trigger does not insert into audit log'
  SELECT @statement = 'Create trigger tr' + @tablename + 'Delete on ' + @tablename + ' for delete as if exists(select sz30tablename from auditlist where sz30tablename=''' + @tablename + ''') insert into ' + @tablename + '_a (' + @colnames+ @addcols + ') select ' + @colnames +',user_name(),''D'',host_name(),getdate() from deleted'
  print @statement
  print 'go'
 end
 select @id = Updtrig from sysobjects where name = @tablename and type = 'U'
 if @id = 0
 begin
  print '--Warning this table has no update trigger - Printing please run'
  SELECT @statement = 'Create trigger tr' + @tablename + 'Update on ' + @tablename + ' for update as if exists(select sz30tablename from auditlist where sz30tablename=''' + @tablename + ''') insert into ' + @tablename + '_a (' + @colnames+ @addcols + ') select ' + @colnames +',user_name(),''U'',host_name(),getdate() from inserted'
  print @statement
  print 'go'
 end
 else
 begin
  if not exists(select * from sysdepends where depid = object_id('auditlist') and id = @id)
   print '--Warning this table''s update trigger does access audit log'
  if not exists(select * from sysdepends where depid = object_id(@audittable) and id = @id)
   print '--Warning this table''s update trigger does not insert into audit log'
  SELECT @statement = 'Create trigger tr' + @tablename + 'Update on ' + @tablename + ' for update as if exists(select sz30tablename from auditlist where sz30tablename=''' + @tablename + ''') insert into ' + @tablename + '_a (' + @colnames+ @addcols + ') select ' + @colnames +',user_name(),''U'',host_name(),getdate() from inserted'
  print @statement
  print 'go'
 end
 select @id = Instrig from sysobjects where name = @tablename and type = 'U'
 if @id = 0
 begin
  print '--Warning this table has no insert trigger - Printing please run'
  SELECT @statement = 'Create trigger tr' + @tablename + 'Insert on ' + @tablename + ' for insert as if exists(select sz30tablename from auditlist where sz30tablename=''' + @tablename + ''') insert into ' + @tablename + '_a (' + @colnames+ @addcols + ') select ' + @colnames +',user_name(),''I'',host_name(),getdate() from inserted'
  print @statement
  print 'go'
 end
 else
 begin
  if not exists(select * from sysdepends where depid = object_id('auditlist') and id = @id)
   print '--Warning this table''s insert trigger does access audit log'
  if not exists(select * from sysdepends where depid = object_id(@audittable) and id = @id)
   print '--Warning this table''s insert trigger does not insert into audit log'
  SELECT @statement = 'Create trigger tr' + @tablename + 'Insert on ' + @tablename + ' for insert as if exists(select sz30tablename from auditlist where sz30tablename=''' + @tablename + ''') insert into ' + @tablename + '_a (' + @colnames+ @addcols + ') select ' + @colnames +',user_name(),''I'',host_name(),getdate() from inserted'
  print @statement
  print 'go'
 end
 if @status = 0
 begin
  exec sp_dboption @databasename, 'select into/bulkcopy',FALSE
 end

GO


then run proc

exec sp_addaudittable 'tablename'

This will create a table tablename_a

it will then print the code to create the triggers, copy this out and run it into the database you now have the triggers for audit.


SET
GO
SET
GO
SET
GO
CREATE

Wednesday, April 18, 2012

how to create an audit trigger for sql agent jobs

Tired of jobs being changed without your knowledge, here is a sample of a audit of the sysjobs view in MSSQL 2008 this will audit update, delete and insert and tell you the time and the hostname and user who changed the table. You can add more audits too.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[sysjobs_a](
[job_id] [uniqueidentifier] NOT NULL,
[originating_server_id] [int] NOT NULL,
[name] [sysname] NOT NULL,
[enabled] [tinyint] NOT NULL,
[description] [nvarchar](512) NULL,
[start_step_id] [int] NOT NULL,
[category_id] [int] NOT NULL,
[owner_sid] [varbinary](85) NOT NULL,
[notify_level_eventlog] [int] NOT NULL,
[notify_level_email] [int] NOT NULL,
[notify_level_netsend] [int] NOT NULL,
[notify_level_page] [int] NOT NULL,
[notify_email_operator_id] [int] NOT NULL,
[notify_netsend_operator_id] [int] NOT NULL,
[notify_page_operator_id] [int] NOT NULL,
[delete_level] [int] NOT NULL,
[date_created] [datetime] NOT NULL,
[date_modified] [datetime] NOT NULL,
[version_number] [int] NOT NULL,
[sz30auditusername] [varchar](30) NULL,
[sz1auditType] [char](1) NULL,
[sz30audithostname] [char](30) NULL,
[dtaudittimestamp] [datetime] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Auditlist](
[sz30tablename] [varchar](128) NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

insert into auditlist values ('sysjobs')
go


Create trigger trsysjobsDelete on sysjobs for delete as if exists(select sz30tablename from auditlist where sz30tablename='sysjobs') insert into sysjobs_a ([job_id],[originating_server_id],[name],[enabled],[description],[start_step_id],[category_id],[owner_sid],[notify_level_eventlog],[notify_level_email],[notify_level_netsend],[notify_level_page],[notify_email_operator_id],[notify_netsend_operator_id],[notify_page_operator_id],[delete_level],[date_created],[date_modified],[version_number],[sz30auditusername],[sz1auditType],[sz30audithostname],[dtaudittimestamp]) select [job_id],[originating_server_id],[name],[enabled],[description],[start_step_id],[category_id],[owner_sid],[notify_level_eventlog],[notify_level_email],[notify_level_netsend],[notify_level_page],[notify_email_operator_id],[notify_netsend_operator_id],[notify_page_operator_id],[delete_level],[date_created],[date_modified],[version_number],user_name(),'D',host_name(),getdate() from deleted
go
--Warning this table has no update trigger - Printing please run
Create trigger trsysjobsUpdate on sysjobs for update as if exists(select sz30tablename from auditlist where sz30tablename='sysjobs') insert into sysjobs_a ([job_id],[originating_server_id],[name],[enabled],[description],[start_step_id],[category_id],[owner_sid],[notify_level_eventlog],[notify_level_email],[notify_level_netsend],[notify_level_page],[notify_email_operator_id],[notify_netsend_operator_id],[notify_page_operator_id],[delete_level],[date_created],[date_modified],[version_number],[sz30auditusername],[sz1auditType],[sz30audithostname],[dtaudittimestamp]) select [job_id],[originating_server_id],[name],[enabled],[description],[start_step_id],[category_id],[owner_sid],[notify_level_eventlog],[notify_level_email],[notify_level_netsend],[notify_level_page],[notify_email_operator_id],[notify_netsend_operator_id],[notify_page_operator_id],[delete_level],[date_created],[date_modified],[version_number],user_name(),'D',host_name(),getdate() from inserted
go
--Warning this table has no insert trigger - Printing please run
Create trigger trsysjobsInsert on sysjobs for insert as if exists(select sz30tablename from auditlist where sz30tablename='sysjobs') insert into sysjobs_a ([job_id],[originating_server_id],[name],[enabled],[description],[start_step_id],[category_id],[owner_sid],[notify_level_eventlog],[notify_level_email],[notify_level_netsend],[notify_level_page],[notify_email_operator_id],[notify_netsend_operator_id],[notify_page_operator_id],[delete_level],[date_created],[date_modified],[version_number],[sz30auditusername],[sz1auditType],[sz30audithostname],[dtaudittimestamp]) select [job_id],[originating_server_id],[name],[enabled],[description],[start_step_id],[category_id],[owner_sid],[notify_level_eventlog],[notify_level_email],[notify_level_netsend],[notify_level_page],[notify_email_operator_id],[notify_netsend_operator_id],[notify_page_operator_id],[delete_level],[date_created],[date_modified],[version_number],user_name(),'D',host_name(),getdate() from inserted
go

Friday, March 16, 2012

c# totals

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

namespace WindowsFormsApplication1

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

private int value1 = 0;

private int value2 = 0;

private int value3 = 0;

private int value4 = 0;

private void textBox1_TextChanged(object sender, EventArgs e)

{

int i = 0;

if(int.TryParse(textBox1.Text, out i))

{

value1 = i;

DoIt();

}

else

{

textBox1.Text = value1.ToString();

}

}

private void textBox2_TextChanged(object sender, EventArgs e)

{

int i = 0;

if (int.TryParse(textBox2.Text, out i))

{

value2 = i;

DoIt();

}

else

{

textBox2.Text = value2.ToString();

}

}

private void textBox3_TextChanged(object sender, EventArgs e)

{

int i = 0;

if (int.TryParse(textBox3.Text, out i))

{

value3 = i;

DoIt();

}

else

{

textBox3.Text = value3.ToString();

}

}

private void textBox4_TextChanged(object sender, EventArgs e)

{

int i = 0;

if (int.TryParse(textBox4.Text, out i))

{

value4 = i;

DoIt();

}

else

{

textBox4.Text = value4.ToString();

}

}



private void DoIt()

{

textBox5.Text = (value1 + value2 + value3 + value4).ToString();

}





}

}

Wednesday, February 29, 2012

exporting sql statement to excel in c#

a sample of an export from c# using a sql statement to excel


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;




namespace WindowsFormsApplication6
{
public partial class excelout : Form
{
public excelout()
{
InitializeComponent();
}

private void excelout_Load(object sender, EventArgs e)
{

}

private void button1_Click(object sender, EventArgs e)
{
SqlConnection cnn;
string connectionString = null;
string sql = null;
string data = null;
int i = 0;
int j = 0;

Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;

xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

connectionString = "data source=192.168.20.3;initial catalog=hct;user id=ted;password=ted;";
cnn = new SqlConnection(connectionString);
cnn.Open();
sql = "SELECT * FROM Product1";
SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
DataSet ds = new DataSet();
dscmd.Fill(ds);

for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
{
data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
xlWorkSheet.Cells[i + 1, j + 1] = data;
}
}

xlWorkBook.SaveAs("C:\\ted.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();

releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);

MessageBox.Show("Excel file created , you can find the file c:\\ted.xls");


}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}

}

}
}

Wednesday, December 22, 2010

Table ' does not have the identity property. Cannot perform SET operation.

Error Message:


System.Data.SqlClient.SqlException: Cannot find the object "table" because it does not exist or you do not have permissions.

try again
System.Data.SqlClient.SqlException: Table table does not have the identity property. Cannot perform SET operation.
at table.DatabaseIO.UpdateKey(Key key, KeyGroup keyGroup)
at table.Interface.AddKey.btnSave_Click(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at DevExpress.XtraEditors.BaseButton.OnClick(EventArgs e)
Description:
This error message appears when you try to use the SET IDENTITY_INSERT setting for a table that does not contain a column, for which the IDENTITY property was declared.

Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.

Resolution:
Error of the Severity level 16 are generated by the user and are corrigible by the user. The SET IDENTITY_INSERT setting cannot be used on such a table.

Versions:
All versions of SQL Server.

Example(s):
USE test
GO

CREATE TABLE test2
(
test1 int)
GO
SET IDENTITY_INSERT t ON
INSERT INTO test1 SELECT 1
SET IDENTITY_INSERT test1 OFF
DROP TABLE test1
GO

Remarks:
In the above example we try to turn on the IDENTITY_INSERT setting für the table test1 to insert an explicite value into a column for which the IDENTITY property was declared. Because there no such column in table test1, the error is raised.

How to enable access to sql proflier without sysadmin

If a member of your team wants to have trace rights to diag a issue you can grant alter trace.

grant Alter trace to username
go

to stop this right

deny alter trace to username
go