Differences

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

--- dba:mssql:mssql_basic_queries [2023/08/14 08:04] – 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 32: / Line 33: @@
 </code>
+===== Databases =====
+==== List databases ====
+<code sql>
+select database_id,
+    name,
+    collation_name,
+    user_access,
+    is_read_only
+from sys.databases;
+</code>
+===== Log =====
+==== Event log ====
+While connected to a database and using ''master''
+<code sql>
+select *
+from sys.event_log;
+</code>
+==== Last hour events ====
+<code sql>
+SELECT
+    *
+FROM
+    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>