# Tahir Hassan's Blog

My Technical Notes

## Thursday, 10 March 2016

### SQL Server Security Model

#### Heirarchical Organisation

There are different levels of organisation within SQL Server. A Server contains databases, a database contains schemas and schemas contain tables, stored procedures, functions and so on. We can represent this by talking about the $[Things] that something contains.$[ServerThings] include $[Databases],$[DatabaseThings] include $[Schemas],$[SchemaThings] include $[Tables]. All things are a union of these three sets (levels of organisation) $$Things = ServerThings \cup DatabaseThings \cup SchemaThings$$ If we use$[*] to represent things at a level or below that level we can see: $$SchemaThings^* \subset DatabaseThings^* \subset ServerThings^*$$

#### Schemas

Schemas are a $[DatabaseThing]. Given a$[Database\ d], we can take its schemas. Schemas include the familiar $[dbo]: $$schemas(d) = \{ dbo, \ldots \}$$ We let$[Schemas] denote this set for the database under consideration:

$$Schemas \eqdef schemas(d)$$

#### Objects

Objects are contained within a schema, therefore they are $[SchemaThings]. Objects include things like tables, views, stored prodedures and so on. Given a$[Schema\ s] We can take its objects: $$objects(s) = tables(s) \cup views(s) \cup storedprocs(s) \cup \ldots$$

We use $[Objects] when the$[Schema\ s] is understood:

$$Objects \eqdef objects(s)$$

Given an $[o \in Objects], we can take it's schema: $$schema(o) = s$$ Given an$[o \in objects(s)] we can say that $[o] is in$[s]'s scope.

Logins are $[ServerThings]. On installation, an$[sa] login will be created (System Administrator). Apparently, their correct name is Server Principles. Given a $[Server\ S], we can take its logins: $$logins(S) = \{ sa, \ldots \}$$ We let$[Logins] denote this set when the server $[S] is understood: $$Logins \eqdef logins(S)$$ #### Users Users are$[DatabaseThings]. However, a $[User\ u] is only valid if they exist in$[Logins].

$$validUser(u) \iff u \in Logins$$

In order to create a user for a database, we have to first create a login and then create the user.

In the case where we restore a database but a $[User\ u] does not exist in$[Logins] then \$[\neg validUser(u)] holds. Such a user is called an orphaned user.

TODO: Complete this.