dba:mssql:mssql_basic_queries
Table of Contents
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