Differences

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

--- dba:mssql:mssql_basic_queries [2023/08/14 08:03] – dodger
+++ dba:mssql:mssql_basic_queries [2023/08/14 17:30] – dodger
@@ Line 1: / Line 1: @@
 ====== MSSQL: Basic queries ======
+====== Information ======
-===== DBA queries =====
+===== Users =====
-==== Users ====
+==== All users info ====
-=== All user info ===
 <code sql>
@@ Line 13: / Line 13: @@
 </code>
+==== Users with access ====
-=== Users with access ===
 <code sql>
 SELECT
@@ Line 24: / Line 23: @@
 </code>
-=== Non mssql internal users ===
+==== Non mssql internal users ====
 <code sql>
@@ Line 34: / 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 ()));
+</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>