Use this T-SQL to find this out and kick ’em off 😉 !

select name
from master.. syslogins 
where sysadmin = '1'

Its important for security to monitor unused logins on SQL. Only logins which need to be there should be there. There are a number of ways to monitor the which logins are using used but these methods vary across the versions of SQL, it can be handy to have a basic standard method which a DBA is confidence possesses interoperability across all versions of SQL. Below i’ve outlined a really simple way the sys.sysprocesses table can be used to capture logon data which could be used to find logins which aren’t being used

1) Create a table to hold the audit data

create table record_login_user_behaviour (

loginame         varchar(50 ),
cmd              varchar(200 ),
database_name    varchar(50 ),
frequency        int,
date_captured    datetime

)

2) Create a stored procedure to capture login names of currently running processes. I included a count of the commands being used and the time, you may want to tweak for your needs.

USE <<database>>
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure mon_record_login_user_behaviour

as
set nocount on

insert into record_login_user_behaviour
select distinct loginame,cmd , DB_NAME (DBID) as database_name , COUNT(loginame ) as frequency, GETDATE() as date_captured
from sys .sysprocesses
where loginame <> 'sa'
group by loginame, cmd , dbid
GO

3) Attach capture procedure to job to regularly grab what logins are being used.

4) The script below will compare what logins have been captured as being used against the logins on SQL and produces a list of what what logins haven’t been used.

select distinct name
from sys .server_permissions as spe
inner join sys. server_principals as spr
on spe. grantee_principal_id  = spr .principal_id
where name not in (select distinct loginame from record_login_user_behaviour )

Any ways of improving this activity would be gratefully recieved :-)

 

 

Nice and quick this one . . . .

The T-SQL below brings together a number of meta tables in SQL to present all the permissions all users have against every schema and table/column in a database.

 select prin. name, prin .type_desc, per.class_desc , per. permission_name, per.state_desc, sch.name
from sys .database_principals as prin
inner join sys. database_permissions per
on prin. principal_id = per .grantee_principal_id
       left outer join sys.schemas sch
       on per. major_id = sch .schema_id and prin. name <> 'public'
where  class_desc = 'SCHEMA'
union
select prin. name, prin .type_desc, per.class_desc , per. permission_name, per.state_desc, isnull( obj.name , 'database_name' )
from sys .database_principals as prin
inner join sys. database_permissions per
on prin. principal_id = per .grantee_principal_id
       left outer join sys.objects obj
       on per. major_id = obj .object_id
where class_desc <> 'SCHEMA' and prin. name <> 'public'
order by prin.name

Moving Tempdb

You might want to move tempdb for a variety of reason, sizing being the most likely issue. Here’s how to do it using T-SQL

1) use tempdb. execute sp_helpfile to show the files associated to the database.

USE TempDB

EXEC sp_helpfile -- this will tell you want the names of the files to move are

2) Use the master database and alter the tempdb to change the location of the files

USE master

ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = '<<WHERE YOU WANT THE DATA FILE TO GO>>\tempdb.mdf')

ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = '<<WHERE YOU WANT THE LOG FILE TO GO>>\templog.ldf')

3) A message will be displayed informing us that the files have been modified in the catalogue but no changes will be made until the database is restarted.

4) Restart SQL and the changes will be made.

Obviously, make sure your restart isn’t going to affect production systems 😉

 

 

From MS SQL 2008 DBAs can make use of SQL Server Audits. This feature allows you to audit the interactions against objects in your system. For example, a SQL audit could be used to monitor SELECTS, INSERTS, DELETES and UPDATES against a particular database.

Below is an example of the T-SQL required to perform an audit against a whole database

1) First create the audit against the MASTER database.

USE MASTER
CREATE SERVER AUDIT TEST_AUDIT
TO FILE ( FILEPATH = '<<PATH_TO_AUDIT_FILE>>' )
WITH ( QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE, STATE = ON)

Check in the Security –> Audit you’ll see the audit.

2) Against the database you want to audit create the audit specification

USE <<database>>

CREATE DATABASE AUDIT SPECIFICATION TEST_AUDIT_SPECIFICATION
FOR SERVER AUDIT TEST_AUDIT
ADD (SELECT
ON DATABASE ::<<database>>
BY PUBLIC )
WITH (STATE = ON)

Check in the  <<database>> –> Security –> Database Audit Specification you’ll see the audit.

A file (with the extension .sqlaudit) will be created to at the location you specified with the audit data. T-SQL can be used to view the contents of this file.

SELECT session_server_principal_name, database_name, object_name, statement, event_time
FROM sys .fn_get_audit_file ('<<PATH_TO_AUDIT_FILE>>' ,default, default)

 

A quickie . . .

Did you know that sp_spaceused can tell you the number of rows in a database using a lot less effort than using count()?

Count() will go through the whole table counting every row, sp_spaceused however will use meta data about the table to ascertain how many rows there are.

Might be useful 😉

 

First set the database in question to single user to ensure no other connections are made to it

Then examine sys.database_files to get the file names of the database. Make a note of these names

USE <<database>>
select *
from sys .database_files

Detach the database using sp_detach_db.

EXEC sp_detach_db '<<database_name>>', 'true'

Refreshing the databases tab in SSMS will show the database disappear. It has not been deleted rather detached from management studio.

Now move the MDF and LDF files to your desired location.

Attach your database files using sp_attach_db.

EXEC sp_attach_db @dbname = N'<<database_name>>',
    @filename1 = N'M:\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Data\database_DB.mdf',
    @filename2 = N'M:\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Data\database_DB_log.ldf'

 

Refresh databases and you’ll see your newly moved databases appear in SSMS.

 

 

Lets say you’ve changed the name of your physical server and you want your sql server instance name to reflect this.

--THIS WILL SHOW YOU THE SERVERS 
select *
from sys .servers

--THIS WILL DROP THE SERVER FROM THE SERVER TABLE 
exec sp_dropserver 'OLD_SERVER_NAME'

--THIS WILL ADD THE NEW SERVER TO THE SERVERS TABLE, LOCAL WILL MAKE THIS SERVER_ID 0, THE INSTANCE YOU'RE ON
exec sp_addserver 'NEW_SERVER_NAME', 'local'

 

It goes without saying, be careful. Think carefully about what you’re dropping and adding 😉