Ciberterminal Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== MSSQL: Basic queries ======
====== Information ======

===== Users =====
==== All users info ====

<code sql>
SELECT
    *
FROM
    sysusers
;
</code>

==== Users with access ====
<code sql>
SELECT
    *
FROM
    sysusers
WHERE
    hasdbaccess = 1;
</code>

==== Non mssql internal users ====

<code sql>
SELECT
    *
FROM
    sysusers
;
</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>