User Tools

Site Tools


dba:mssql:mssql_basic_queries

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
dba/mssql/mssql_basic_queries.txt · Last modified: 2023/08/15 08:59 by dodger