dba:mssql:mssql_basic_queries
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
dba:mssql:mssql_basic_queries [2023/08/14 08:03] – dodger | dba:mssql:mssql_basic_queries [2023/08/15 08:59] (current) – [Last hour events] dodger | ||
---|---|---|---|
Line 1: | Line 1: | ||
====== MSSQL: Basic queries ====== | ====== MSSQL: Basic queries ====== | ||
+ | ====== Information ====== | ||
- | ===== DBA queries | + | ===== Users ===== |
- | ==== Users ==== | + | ==== All users info ==== |
- | === All user info === | + | |
<code sql> | <code sql> | ||
Line 13: | Line 13: | ||
</ | </ | ||
- | + | ==== Users with access | |
- | === Users with access === | + | |
<code sql> | <code sql> | ||
SELECT | SELECT | ||
Line 24: | Line 23: | ||
</ | </ | ||
- | === Non mssql internal users === | + | ==== Non mssql internal users ==== |
<code sql> | <code sql> | ||
Line 34: | Line 33: | ||
</ | </ | ||
+ | |||
+ | ===== Databases ===== | ||
+ | ==== List databases ==== | ||
+ | <code sql> | ||
+ | select database_id, | ||
+ | name, | ||
+ | collation_name, | ||
+ | user_access, | ||
+ | is_read_only | ||
+ | from sys.databases; | ||
+ | </ | ||
+ | |||
+ | ===== Log ===== | ||
+ | ==== Event log ==== | ||
+ | While connected to a database and using '' | ||
+ | <code sql> | ||
+ | select * | ||
+ | from sys.event_log; | ||
+ | </ | ||
+ | |||
+ | ==== Last hour events ==== | ||
+ | |||
+ | <code sql> | ||
+ | SELECT | ||
+ | * | ||
+ | FROM | ||
+ | sys.event_log | ||
+ | WHERE | ||
+ | start_time > DateAdd (HOUR, -1, CONVERT(datetime2, | ||
+ | ORDER BY start_time DESC; | ||
+ | </ | ||
+ | |||
+ | |||
+ | ==== Not connection succeed events ==== | ||
+ | |||
+ | <code sql> | ||
+ | SELECT | ||
+ | * | ||
+ | FROM | ||
+ | sys.event_log | ||
+ | WHERE | ||
+ | event_subtype_desc <> ' | ||
+ | ORDER BY | ||
+ | start_time DESC OFFSET 11 ROWS FETCH NEXT 10 ROWS ONLY; | ||
+ | </ | ||
+ | |||
+ | ====== Administration ====== | ||
+ | |||
+ | ===== Create user ===== | ||
+ | <code sql> | ||
+ | CREATE USER [dodger] WITH PASSWORD=' | ||
+ | GO | ||
+ | </ | ||
+ | ===== add user to role ===== | ||
+ | <code sql> | ||
+ | sys.sp_addrolemember @rolename = N' | ||
+ | GO | ||
+ | </ | ||
dba/mssql/mssql_basic_queries.1692000228.txt.gz · Last modified: 2023/08/14 08:03 by dodger