Differences

This shows you the differences between two versions of the page.

--- dba:mssql:mssql_basic_queries [2023/08/14 16:29] – dodger
+++ dba:mssql:mssql_basic_queries [2023/08/15 08:59] (current) – [Last hour events] dodger
@@ Line 1: / Line 1: @@
 ====== MSSQL: Basic queries ======
+====== Information ======
 ===== Users =====
-==== All user info ====
+==== All users info ====
 <code sql>
@@ Line 31: / Line 32: @@
 ;
 </code>
 ===== Databases =====
 ==== List databases ====
@@ Line 53: / Line 56: @@
 <code sql>
-select *
+SELECT
-from
+    *
-    sys.event_log
+FROM
-where start_time>DateAdd(hour, -1, Convert(datetime2,SYSDATETIME()));
+    sys.event_log
+WHERE
+    start_time > DateAdd (HOUR, -1, CONVERT(datetime2, SYSDATETIME ()))
+ORDER BY start_time DESC;
 </code>
+==== Not connection succeed events ====
+<code sql>
+SELECT
+    *
+FROM
+    sys.event_log
+WHERE
+    event_subtype_desc <> 'connection_successful'
+ORDER BY
+    start_time DESC OFFSET 11 ROWS FETCH NEXT 10 ROWS ONLY;
+</code>
+====== Administration ======
+===== Create user =====
+<code sql>
+CREATE USER [dodger] WITH PASSWORD='strong_password', DEFAULT_SCHEMA=[the_schema]
+GO
+</code>
+===== add user to role =====
+<code sql>
+sys.sp_addrolemember @rolename = N'db_owner', @membername = N'dodger'
+GO
+</code>