Sql Server 7 - Security

Front 1

what are statement permissions?

Back 1

create statements: CREATE TABLE, CREATE VIEW BACKUP DB, etc.

Front 2

what are object permissions?

Back 2

select, insert, update, delete, reference, excecute.

example: revoke select on authors to [user].

Front 3

fixed database role: public

Back 3

default permissions-- all users are members of the public role.

Front 4

fixed database role: db_denydatawriter

Back 4

memebers of this role cannot make any data modifications.

Front 5

fixed database role: db_denydatareader

Back 5

members of this role cannot read data.

Front 6

fixed database role: db_securityadmin

Back 6

emmbers can add and remove users from roles and manage statement and object permissions.

Front 7

fixed database role: db_ddladmin

Back 7

members can issue ddl commands. doesn't allow viewing

Front 8

fixed database role: db_datawriter

Back 8

members can modify and delete data

Front 9

fixed database role: db_datareader

Back 9

members can read data from all tables

Front 10

fixed database role: db_owner

Back 10

members can do all of the things of all other roles + some admin stuff

Front 11

3 types of database roles

Back 11

1. fixed
2. custom
3. application

Front 12

what is the guest user account

Back 12

used for people that have a SQL Server account but not a user account for a db.

Front 13

sysadmin & DBO

Back 13

members of sysadmin automatically become the DBO in every db

Front 14

Bultin\administrators & NT admins

Back 14

Bultin\administrators members are automatically members of sysadmin. This is potentially bad. Bultin\administrators is usually removed from sysadmin fixed server role.

Front 15

Fixed Server Role: sysadmin

Back 15

SQL Server super users

Front 16

what are fixed server roles used for?

Back 16

to limit the amount of administrative access a user has once logged in to SQL Server

Front 17

What is a users default database?

Back 17

the db to which they automatically connect when connecting to SQL Server. they are automatically granted permission to this db when created.

Front 18

What are the 3 ways NT logins can be mapped to SQL Server logins?

Back 18

1. via a single NT user
2. via a NT Group
3. via a builtin NT group

Front 19

how does mixed mode work?

Back 19

1. user logs into the network, nt or other.
2. user opens a non-trusted connection to SQL Server
3. SQL Server verifies user and password in syslogins table

Front 20

How does Windows NT authentication mode work?

Back 20

1. user logs in and is verified by NT
2. user opens a trusted connection to SQL Server
3. SQL Server tries to match the user name or group membership to an entry in syslogins.
4. if found SQL Server does not need to verify pword because it trusts NT

Front 21

orphaned accounts
sp_validatelogins

Back 21

--occur when restoring a db into another domain
-- reports on orphans

Front 22

Fixed Server Role: processadmin

Back 22

members can end processes

Front 23

Fixed Server Role: securityadmin

Back 23

members can create and delete logins, read the audit log and grand permissions to create databases

Front 24

Fixed Server Role: dbcreator

Back 24

members can create and make changes to a database and can restore backups.

Front 25

Fixed Server Role: diskadmin

Back 25

members can manage files on disk

Front 26

fixed database role: db_accessadmin

Back 26

member can determine who gets access to the database

Front 27

fixed database role: db_backupoperator

Back 27

members can backup database

Front 28

SQLAgentCmdExec

Back 28

account under which cmdexec and activeX scripts are run in jobs run by users not in the sysadmin role.

Front 29

Fixed Server Role: serveradmin

Back 29

members can set server-wide configuration options

Front 30

Fixed Server Role: setupadmin

Back 30

members can manage linked servers