dba:mssql:mssql_basic_queries
Differences
This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
dba:mssql:mssql_basic_queries [2023/08/14 07:21] – created 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 ====== | ||
+ | |||
+ | ===== Users ===== | ||
+ | ==== All users info ==== | ||
+ | |||
+ | <code sql> | ||
+ | SELECT | ||
+ | * | ||
+ | FROM | ||
+ | sysusers | ||
+ | ; | ||
+ | </ | ||
+ | |||
+ | ==== Users with access ==== | ||
+ | <code sql> | ||
+ | SELECT | ||
+ | * | ||
+ | FROM | ||
+ | sysusers | ||
+ | WHERE | ||
+ | hasdbaccess = 1; | ||
+ | </ | ||
+ | |||
+ | ==== Non mssql internal users ==== | ||
+ | |||
+ | <code sql> | ||
+ | SELECT | ||
+ | * | ||
+ | FROM | ||
+ | sysusers | ||
+ | ; | ||
+ | </ | ||
+ | |||
+ | |||
+ | ===== 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.1691997689.txt.gz · Last modified: 2023/08/14 07:21 by dodger