DDL EVENTS List - SQL Server 2008/R2

Below are the all events with their hierarchy and scope that you can use to implement DDL triggers at both Server or Database Level.
You can create trigger to fire on all events defined under a group, then you can create trigger for that particular group like "DDL_TABLE_EVENTS", this trigger will fire on all three sub-events defined under this group; these are CREATE_TABLE, ALTER_TABLE and DROP_TABLE. Similarly if you want to have a trigger to fire only for a particular event in an event group, then specify only that particular event like "ALTER_TABLE".
See example below as well:

Example 01: Database Level Trigger for a particular event

-- DDL Trigger to prevent column changes on a Database

CREATE TRIGGER ColumnChanges

ON DATABASE

FOR ALTER_TABLE

AS

BEGIN

-- Detect whether a column was created/altered/dropped.

SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)')

RAISERROR ('Table schema cannot be modified in this database.', 16, 1);

ROLLBACK;

END

GO

Example 02: Server Level Trigger for a particular event

-- DDL Trigger to prevent column changes for all databases on an instance

CREATE TRIGGER ColumnChanges

ON ALL SERVER

FOR ALTER_TABLE

AS

BEGIN

-- Detect whether a column was created/altered/dropped.

SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)')

RAISERROR ('Table schema cannot be modified in this database.', 16, 1);

ROLLBACK;

END

GO

Script: view information of currently running SQL Server Jobs

Below script returns five (5) columns result set as output. You can further enhance this query to accommodate your requirement.

1. Job_Name: This is job’s registered name on the server’s instance.
2. Last_Executed_Step_ID: This is the ID of the last executed step in the job. E.g. if a job has 4 steps, and currently it is running 3^rd step, so last executed step will be 2 and it will be populated with 2 against Job’s 2^nd step row, while all other steps rows will have 0.
3. Step_ID: This query returns all steps included in the job along with their id, while 2^nd column, returns id same as in this column.
4. Step_Name: This reflects job step name that was given during job creation.
5. Start_Execution_Date: This column returns execution date and time of this job. So you can get an idea when this started execution.

SELECT 
LTRIM(RTRIM(CONVERT(VARCHAR(100),j.name))) job_name

,ISNULL(a.last_executed_step_id,'') last_executed_step_id

,s.step_id

,RTRIM(CONVERT(VARCHAR(100),s.step_name))step_name

,a.start_execution_Date

FROM msdb.dbo.sysjobs j WITH (NOLOCK)

INNER JOIN msdb.dbo.sysjobsteps s WITH (NOLOCK) ON j.job_id = s.job_id

LEFT OUTER JOIN msdb.dbo.sysjobactivity a WITH (NOLOCK) ON j.job_id=a.job_id

AND (s.step_id=a.last_executed_step_id OR a.last_executed_Step_id IS NULL)

AND run_requested_date>=CAST(CONVERT(VARCHAR,GETDATE(),101) AS DATETIME)

AND stop_execution_Date IS NULL

WHERE j.enabled=1

AND j.category_id IN (0,1,2,3,4,5,98,99)

AND j.job_id in 

(SELECT job_id FROM msdb.dbo.sysjobactivity WITH (NOLOCK)

WHERE start_execution_date>= CAST(CONVERT(VARCHAR,GETDATE(),101) AS DATETIME) 

AND stop_execution_date IS NULL )

ORDER BY 1,3

Moving database files

We have three kinds of database files: log files (*.ldf), primary data files (*.mdf) and secondary data files (*.ndf).

Primary data file is the starting point for a database and points to all other files in it. Each database has one primary data file. There can be 0 or >0 secondary data files in a database to make up data files other than primary data file. Log files hold all log information that is used to recover database. There must be at least one log file for a database but it can be more than one.

If you initially create a database by using default locations or defining new one at run time. After sometime if you want to move database files to a new location by considering capacity of current disk or for any other system maintenance.

How you will move them?

Let’s see below!

We have two considerations to move database files from one location to another.
a) Planned relocation procedure
b) Relocation for scheduled disk maintenance

Although you can follow both procedures alternatively, but it depends on the environment and scenario in which you are working. If your concern with only db files move then “Planned relocation procedure” is most suitable in every scenario, because it affects the processes only for relevant database, till the time it comes online.

Planned relocation procedure:
You can follow this procedure when you require less downtime for production, or to minimize relocation impact on other process like replication, log shipping etc, mostly when you want to move files permanently to another location without affecting other databases.

Steps are:
1) Run ALTER DATABASE [database name] SET OFFLINE.
2) Move file to new location
3) Run ALTER DATABASE [database name] MODIFY FILE ( NAME = [logical name], FILENAME = 'path and filename'.
4) Run ALTER DATABASE [database name] SET ONLINE.


Relocation for scheduled disk maintenance:
You can follow this procedure, when you need to move database files to another location to perform any server or disk maintenance activity, like disk de-fragment and you can bear downtime for all databases available on that particular server.


Steps are:
1) Run ALTER DATABASE [database name] MODIFY FILE ( NAME = [logical name], FILENAME = 'path and filename'.
2) Sql server is stopped or system is shutdown to perform maintenance.
3) Move file to new location.
4) Restart server.

Script to get Login's Server and Database level permissions

We have two levels for login

Server Level
Database Level

We can further divide them into Fixed roles and Permissions for a single login as: 

Server Level Permissions
Server Level Fixed Roles
Database Level Permissions
Database Level Fixed Roles

I developed a script to get such details in current environment or on current instance for all databases. Let me know if you find any issue in it.

below scripts returns following attributes as output.

LoginID: ID for database Level/Server Lever Login
LoginName:
Type_Descr: Login Type, SQL User/ Windows User/ Windows Group
Create Date:
Modify Date:
Class_Descr: Description of class on which permission exist, Database/Schema/Object OR Column etc.
Permission_Name: Permission description that is given to login, it contains both Database/Server Roles and Permissions
State_Desc: Grant/Deny/Revoke
DB: it contains DB name Or Server name, for which login is created
P_Type: it is flag P=Permission and F=Fixed role like db_owner, db_readonly etc.

 

Script:

Begin
Declare @SQL varchar(500)
            ,@Counter int
            ,@Total int
            ,@DB_Name varchar(50)
            ,@Server varchar(50) 

Set @Server = @@ServerName
Create Table #DBPermissions
(      LoginID int
      ,LoginName varchar(255)
      ,Type_desc varchar(255)
      ,Create_Date DateTime
      ,Modify_Date DateTime
      ,class_desc varchar(255)
      ,Permission_name varchar(255)
      ,State_desc varchar(255)
      ,DB varchar(50)
      ,P_Type varchar(1)
)

select identity(int,1,1) id,name,database_id into #DBs from sys.databases

Set @Total = @@RowCount

Set @Counter = 1

While @Counter<=@Total
Begin
      Select @DB_Name = name From #Dbs where id = @Counter

      -- Database Permissions
      Set @SQL = 'select Logins.Principal_ID, Logins.name, Logins.Type_desc, Logins.Create_Date, Logins.Modify_date, P.class_desc, P.Permission_name, P.State_Desc, ''' + @DB_Name + ''',''P'' from ' + @DB_Name + '.sys.database_principals Logins Left Outer Join ' + @DB_Name + '.sys.database_permissions P On P.grantee_principal_id = Logins.Principal_ID          where Logins.type not in (''A'',''R'',''C'',''K'') And P.Permission_name is not null'
      Insert into #DBPermissions
      Exec (@SQL)

-- Database Fixed Role
      Set @SQL = 'select member_principal_id,M.Name , M.type_Desc , M.Create_Date, M.Modify_date, '''', Roles.name, '''', ''' + @DB_Name + ''', ''F'' from ' + @DB_Name + '.sys.database_principals Roles Inner join ' + @DB_Name +.sys.database_role_members DRoles on Droles.role_principal_id = Roles.Principal_ID And Roles.type  in (''A'',''R'',''C'',''K'') Inner join ' + @DB_Name + '.sys.database_principals M On M.Principal_ID = Droles.member_Principal_ID And M.type  not in (''A'',''R'',''C'',''K'')'

      Insert into #DBPermissions
      Exec (@SQL)

      Set @Counter = @Counter + 1

End

-- Server Logins Permission
Insert Into #DBPermissions
select SLogins.Principal_ID, SLogins.name, SLogins.Type_desc, SLogins.Create_Date, SLogins.Modify_date, P.Class_Desc, P.Permission_name, P.State_Desc,@Server,'P'
from sys.server_principals SLogins
Inner Join sys.server_Permissions P on P.grantee_principal_id = SLogins.Principal_ID
where SLogins.type not in ('R','C','K')

-- Server Fixed Roles
Insert Into #DBPermissions
select Roles.member_principal_id,M.Name, SLogins.Type_desc, SLogins.Create_Date, SLogins.Modify_date,'', SLogins.name, '', @Server, 'F'
from sys.server_principals SLogins
Inner join sys.Server_Role_Members Roles On roles.role_principal_id =Logins.Principal_ID And SLogins.type in ('R','C','K')
Inner join sys.server_principals M On  roles.member_principal_id = M.Principal_ID     And M.type not in ('R','C','K') 

SELECT * FROM #DBPERMISSIONS ORDER BY 1

Drop Table #DBPermissions

Drop Table #DBs

End

 

 

Scalar Configuration Functions

All configuration functions are nondeterministic. This means these functions do not always return the same results every time they are called, even with the same set of input values.

The following scalar functions return information about current configuration option settings,

@@DATEFIRST
Returns the current value, for the session, of SET DATEFIRST.
e.g. SELECT @@DATEFIRST
you can change first day of week setting by using the following command.
SET DATEFIRST [integer]
e.g Set DATEFIRST 5
It will set Firday as first day of week.

@@DBTS
It returns the last-used timestamp value of the current database. A new timestamp value is generated when a row with a timestamp column is inserted or updated.
e.g. SELECT @@DBTS

@@LANGID
Returns the local language identifier (ID) of the language that is currently being used.
e.g. SELECT @@LANGID

To view information about language settings, including language ID numbers, you can use the following system procedure.
EXEC SP_HELPLANGUAGE

You can change language settings as
e.g. SET LANGUAGE 'Italian'

@@SERVERNAME
Returns the name of the local server running SQL Server.
e.g. SELECT @@SERVERNAME

@@LANGUAGE
Returns the name of the language currently being used.
e.g. SELECT @@LANGUAGE
Remaining details will be the same as defined under @@LANGID

@@SERVICENAME
Returns the name of the registry key under which SQL Server is running. @@SERVICENAME returns 'MSSQLSERVER' if the current instance is the default instance; this function returns the instance name if the current instance is a named instance.
e.g. SELECT @@SERVICENAME

@@LOCK_TIMEOUT
Returns the current lock time-out setting in milliseconds for the current session.
It allows an application to set the maximum time that a statement waits on a blocked resource. When a statement has waited longer than the LOCK_TIMEOUT setting, the blocked statement is automatically canceled, and an error message is returned to the application.

It returns a value of -1 if SET LOCK_TIMEOUT has not yet been run in the current session.
e.g. SELECT @@LOCK_TIMEOUT

@@SPID
Returns the session ID of the current user process.
It can be used to identify the current user process in the output of sp_who.
e.g. SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'

@@TEXTSIZE
Returns the current value of the TEXTSIZE option of the SET statement. This specifies the maximum length, in bytes, of varchar(max), nvarchar(max), varbinary(max), text, or image data that a SELECT statement returns.
The default size is 4096 bytes. The maximum size that @@TEXTSIZE returns is 2^31-1 bytes.

You can change its setting as:

SET TEXTSIZE [integer]

e.g.
Displays current value
SELECT @@TEXTSIZE AS 'Text Size'
Setting new value
SET TEXTSIZE 2048
Displays new value
SELECT @@TEXTSIZE AS 'Text Size'

@@VERSION
Returns version, processor architecture, build date, and operating system for the current installation of SQL Server.
The information returned by @@VERSION is similar to the product name, version, platform, and file data returned by the xp_msver stored procedure, which provides more detailed information.
e.g. SELECT @@VERSION

List of Permission granted to caller [Calling User]

Following statements returns the permission details for the user who is executing the statement.

-- List of Permission at Server Level
SELECT * FROM fn_my_permissions(NULL, 'SERVER')

-- List of Permission at Database Level
SELECT * FROM fn_my_permissions('DB1', 'DATABASE')

-- List of Permission at Object Level
SELECT * FROM fn_my_permissions('Object1', 'OBJECT')
ORDER BY subentity_name, permission_name

It reports:
Entity_Name
Subentity_Name
Permission_Name