This site is 100% ad supported. Please add an exception to adblock for this site.

SQL Server 7 - Security


undefined, object
copy deck
what are statement permissions?
create statements: CREATE TABLE, CREATE VIEW BACKUP DB, etc.
what are object permissions?
select, insert, update, delete, reference, excecute.

example: revoke select on authors to [user].
fixed database role: public
default permissions-- all users are members of the public role.
fixed database role: db_denydatawriter
memebers of this role cannot make any data modifications.
fixed database role: db_denydatareader
members of this role cannot read data.
fixed database role: db_securityadmin
emmbers can add and remove users from roles and manage statement and object permissions.
fixed database role: db_ddladmin
members can issue ddl commands. doesn't allow viewing
fixed database role: db_datawriter
members can modify and delete data
fixed database role: db_datareader
members can read data from all tables
fixed database role: db_owner
members can do all of the things of all other roles + some admin stuff
3 types of database roles
1. fixed
2. custom
3. application
what is the guest user account
used for people that have a SQL Server account but not a user account for a db.
sysadmin & DBO
members of sysadmin automatically become the DBO in every db
Bultin\administrators & NT admins
Bultin\administrators members are automatically members of sysadmin. This is potentially bad. Bultin\administrators is usually removed from sysadmin fixed server role.
Fixed Server Role: sysadmin
SQL Server super users
what are fixed server roles used for?
to limit the amount of administrative access a user has once logged in to SQL Server
What is a users default database?
the db to which they automatically connect when connecting to SQL Server. they are automatically granted permission to this db when created.
What are the 3 ways NT logins can be mapped to SQL Server logins?
1. via a single NT user
2. via a NT Group
3. via a builtin NT group
how does mixed mode work?
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
How does Windows NT authentication mode work?
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
orphaned accounts
--occur when restoring a db into another domain
-- reports on orphans
Fixed Server Role: processadmin
members can end processes
Fixed Server Role: securityadmin
members can create and delete logins, read the audit log and grand permissions to create databases
Fixed Server Role: dbcreator
members can create and make changes to a database and can restore backups.
Fixed Server Role: diskadmin
members can manage files on disk
fixed database role: db_accessadmin
member can determine who gets access to the database
fixed database role: db_backupoperator
members can backup database
account under which cmdexec and activeX scripts are run in jobs run by users not in the sysadmin role.
Fixed Server Role: serveradmin
members can set server-wide configuration options
Fixed Server Role: setupadmin
members can manage linked servers

Deck Info