Who Dropped The Tables?

Recently, when I did daily checks on ETL processes, and then found one of ETLs failed on a few tables. Later on I realised those tables had been dropped somehow. However no one knew who did it, when did it and why did it. To investigate the crime, SQL Server default trace became the most useful tool, I found.

Firstly, you need to check if ‘default trace enabled’ through the following two ways.

1.      Run sp_configure

-- 'default trace enabled' only shows when 'show advanced options' is enabled

sp_configure 'show advanced options', 1

reconfigure

GO

-- check if 'default trace enabled'has 1 for both 'config_value' and 'run value'

sp_configure

   

2.      Or run the following script to check the setting

 SELECT* FROM sys.configurations WHERE configuration_id = 1568

I Need To Be The Excutor On All The Databases

A developer at work asked me to grant her the permissions that allows her to execute store procedures and view the definitions on all the databases on the server. However, there are over 100 databases on the same server, it would take ages to grant the permissions to her one database by another one. And on some database, she was already db_datareader.

Therefore I decided to create ‘db_excutor’ role with the permission of ‘excute’ and ‘view definition’ on all the  databases, and create new users on those databases that she didn’t have access to…there is my code.

/* Create a database list that the user need get access to */

IF Object_id ('tempdb..#name') IS NOT NULL

        DROP TABLE #name

  SELECT name INTO #name

   FROM master.sys.databases

   WHERE database_id NOT IN (1,2,3,4,6,163) 

 

Finding and Troubleshooting SQL Server Permissions

It can be very tricky to troubleshoot user permissions on SQL Server. As SQL Server DBA, we are quite often questioned by users about what permissions they’ve got.  The permissions can be granted on server, database, schema and database object level (such as table, store proc, views, functions, and etc). To avoid dig through many screens or levels in management studio, fn_my_permission, as a built-in table value function from SQL Server 2005, provides the list of all the permissions effectively granted to the principle on a securable.

Before starting the permission checks, your login needs to be an account that has sysadmin role.

To list all permissions on server:

If login isn't specified, it shows what permissions the caller has by default.

                                                                       

EXECUTE AS LOGIN = 'Domain\UserLogin'

SELECT * FROM fn_my_permissions(NULL, NULL)

REVERT

Revert is to switche the execution context back to the caller of the last EXECUTE AS statement

Or alternative use

SELECT * FROM fn_my_permissions(NULL, 'SERVER')