====== MSSQL: Basic queries ======
====== Information ======
===== Users =====
==== All users info ====
SELECT
*
FROM
sysusers
;
==== Users with access ====
SELECT
*
FROM
sysusers
WHERE
hasdbaccess = 1;
==== Non mssql internal users ====
SELECT
*
FROM
sysusers
;
===== Databases =====
==== List databases ====
select database_id,
name,
collation_name,
user_access,
is_read_only
from sys.databases;
===== Log =====
==== Event log ====
While connected to a database and using ''master''
select *
from sys.event_log;
==== Last hour events ====
SELECT
*
FROM
sys.event_log
WHERE
start_time > DateAdd (HOUR, -1, CONVERT(datetime2, SYSDATETIME ()))
ORDER BY start_time DESC;
==== Not connection succeed events ====
SELECT
*
FROM
sys.event_log
WHERE
event_subtype_desc <> 'connection_successful'
ORDER BY
start_time DESC OFFSET 11 ROWS FETCH NEXT 10 ROWS ONLY;
====== Administration ======
===== Create user =====
CREATE USER [dodger] WITH PASSWORD='strong_password', DEFAULT_SCHEMA=[the_schema]
GO
===== add user to role =====
sys.sp_addrolemember @rolename = N'db_owner', @membername = N'dodger'
GO