Repository navigation
CLI Security
The security group reads the server's principals and their permissions:
logins, the connection database's users (with the orphan check), server and
database roles, a role's members, and what a principal may actually do — the
explicit grants, or the effective answer with roles and DENY resolved. All
five commands are reads: they change nothing, and never need --allow. (The
TUI is where principals are created and edited: see
Security in the TUI.)
gossms security logins list -p prod-ro
gossms security users list -p dev -d AdventureWorks
gossms security roles list -p prod-ro --scope server
gossms security role-members sysadmin --scope server -p prod-ro
gossms security permissions app_user -p dev -d Sales --effectivegossms security logins list -p prod-ro
gossms security logins list -p prod-ro --type sql --include-disabled -o jsonOne row per server principal (sys.server_principals), by name: name,
type (the type_desc: SQL_LOGIN, WINDOWS_LOGIN, WINDOWS_GROUP_LOGIN,
EXTERNAL_LOGIN (Microsoft Entra), …), disabled, system (the ##…## logins
SQL Server creates for itself), default_database, default_language,
policy_checked, expiration_checked (CHECK_POLICY / CHECK_EXPIRATION — a SQL
login's, 0 for every other type), created, modified.
-
--include-disabled: disabled logins are left out without it. -
--type sql|windows|windows-group|external|external-group|certificate|asymmetric-key. - Visibility: a login sees itself and the fixed roles unless it has VIEW ANY DEFINITION, ALTER ANY LOGIN or is sysadmin — the list is then short, not refused (exit 0). Same theme as everywhere: metadata visibility hides, it does not error.
gossms security users list -p dev -d AdventureWorks
gossms security users list -p dev -o jsonThe connection database's users (-d picks it; dbo, guest,
INFORMATION_SCHEMA and sys included): name, type, login (the login
whose SID the user's matches; NULL for none), login_disabled, orphaned,
default_schema, authentication, mapped_to (a certificate or key),
created, modified.
orphaned is:
1 |
A SQL user authenticated INSTANCE whose login no longer matches — the classic orphan after a restore; fix it in the TUI's Security pages or with ALTER USER … WITH LOGIN
|
0 |
Has a login, or needs none (WITHOUT LOGIN, contained) |
NULL |
Cannot be told: a Windows user with no login of its own may enter through a Windows group; and Azure SQL Database hides the server's logins from a user database |
Without VIEW DEFINITION on the database a login sees only itself and the built-in users: short list, exit 0.
gossms security roles list -p dev -d AdventureWorks
gossms security roles list -p prod-ro --scope server -o jsonOne row per role, by name: name, scope, fixed (db_owner, public, sysadmin,
securityadmin…), owner, member_count, members (direct members,
comma-separated; a member role is not expanded — ask for its members in
turn). --scope database (default; the connection's) or server. It is
--scope, not --server — --server is the connection option. Without VIEW
DEFINITION (VIEW ANY DEFINITION for server roles) the login sees the fixed
roles and its own.
gossms security role-members sysadmin --scope server -p prod-ro
gossms security role-members db_owner -p dev -d AdventureWorks -o jsonA record (role in the catalog's spelling, scope, fixedRole) and set
members, by name: name, type — a nested role reads DATABASE_ROLE /
SERVER_ROLE and is not expanded. public lists no members (everyone is in
it). A role missing from the scope asked is NOT_FOUND (exit 8) with a hint
naming the other scope when a role of that name lives there —
"did you mean --scope server?".
# what was granted to this user directly
gossms security permissions app_user -p dev -d Sales
# what the user can actually do, roles and DENY resolved
gossms security permissions app_user -p dev -d Sales --effective
# a server principal
gossms security permissions build_agent --scope server -p prod-ro --effective<principal>: a user, then a database role, of the connection's database;
with --scope server, a login, then a server role. Not found: exit 8, the
hint naming the other scope, or the user the login maps to in this
database. The record: principal, principalType, scope, mode
(explicit/effective), memberOf (direct roles; null for none).
-
Explicit (default) — set
permissions:class(DATABASE,SCHEMA,TABLE,VIEW,PROCEDURE, …,SERVER),schema,name,column,permission,state(GRANT,GRANT_WITH_GRANT_OPTION,DENY) — the principal's own entries, securables then columns. What it holds through a role is deliberately not among them. -
--effective— setpermissions(entity,permission):fn_my_permissionson the database (or server) underEXECUTE AS USER/LOGIN, with roles and DENY resolved. Grants on individual schemas and objects are not part of that function's answer — combine the two modes for the full picture. - A role given as the principal is exit 2 (SQL Server cannot impersonate a
role); no IMPERSONATE permission (Msg 15517/15406) is
PERMISSION_DENIED, exit 9.
# audit: who can kill sessions or change the server
gossms security role-members sysadmin --scope server -p prod -o json | jq -r '.resultSets[0].rows[].name'
gossms security role-members securityadmin --scope server -p prod
# find orphans after a restore
gossms security users list -p dev -d RestoredApp -o json |
jq -r '.resultSets[0].rows[] | select(.orphaned==1) | [.name,.authentication] | @tsv'
# can this login read what monitor needs? compare the model to the answer
gossms agent context -p app -o json | jq '.resultSets[] | select(.name=="missing_permissions")'
gossms security permissions app_user -p app -d Sales --effective -o json |
jq -r '.resultSets[0].rows[].permission' | sort
# disabled logins still pointed at by jobs? (list, then review in the TUI's Agent pages)
gossms security logins list -p prod --include-disabled -o json |
jq -r '.resultSets[0].rows[] | select(.disabled==1) | .name'| Command | A low-privilege login sees |
|---|---|
logins list |
Itself + the fixed server roles (short list, exit 0) |
users list |
Itself + the built-in users (short list, exit 0) |
roles list |
The fixed roles + its own |
role-members |
Only roles it may see; one it cannot is not listed (metadata visibility hides, per the rule above) |
permissions |
Itself, always; anyone else needs IMPERSONATE (else exit 9) |
- Getting connected
- Browsing and editing
- Diagnosing and tuning
- Server features