don’t grant db_owner

Don’t grant sysadmin, I hope that has been made clear in previous posts. If you grant sysadmin you are making a HUGE mistake. Sysadmins can take over the server. Local admins can take over SQL. It does take some effort but Local Administrators and SQL Sysadmins effectively have the same privileges. When it comes to audit time, I believe you might be able to state these two roles as separate but behind the scenes they are the same.

But what about this db_owner? It looks fairly harmless. Lets take a closer look.

How bad is it?

As you can see, we have this new guy that wants to connect to SQL Server. We create him a login on an existing server with topsecret information (ooops #1). We create him a SQL Login jimmy_the_dev (ooops #2). But those two things alone are not all that bad. He can’t do much with just the public role.

Ahhh, I see, you wanted some actual privileges

We do have to grant jimmy some permissions. To do this we should create mappings from the login to a database. If you use the GUI it creates a database user automatically.

If you just select public, SQL creates the database user, but jimmy doesn’t have any useful permissions. He can’t even see the nice new ‘Table_1’ we created for him. He is past the first error but not by much.

Where are my tables?

There are some convenient default database roles. db_datareader is a nice one that will grant read access to everything, including future objects. db_datawriter is the same but for IUDs. Some users will need to be manually granted execute permissions in the database so they can run stored procedures.

My recommendation is to create a few custom database roles and map to those. Unfortunately, time doesn’t always allow for that. New additions to the schema will need to be added to these roles. Maybe now you have time for this manual work now but when the schema changes you might not have that luxury.

The easy button for granting all access to a single database is the role “db_owner” The easy button is sometimes the wrong button to press. Here’s why:

1. a db_owner can create tables (1 trillion row heaps)
2. a db_owner can drop the database (dammit jimmy!)
3. a db_owner can backup the database and break the dba’s differential backups.
4. a db_owner can create users mapped to existing logins, and make them db_owners too!
5. a db_owner can perform maintenance like rebuilding 1 trillion row heaps
6. a db_owner can modify files and grow them to ridiculous sizes
7. a db_owner can add log files on the same drive as data files
8. a db_owner can turn off auto-growth

And maybe the best one of all, a db_owner can drop users… including themselves!

The suicide squeeze.

That said, these permissions are not too devastating. db_owner is not a “take over the world” role. I use it from time to time but the more experienced I get, the less I want to use it. The main reason is if you grant this, no one runs into security problems and they don’t understand how database security works. When it comes time to go live you might have to go through all of the security exercises you skipped by granting db_owner. That is more painful that doing it right in the first place.

 

Thanks SAM

By now it’s probably not that shocking to you to hear about password theft. Everyone knows that the hackers have the ability to steal passwords hoards at a time. What I want people to realize is it can happen to you. Maybe reading this post will shock you into creating stronger passwords BEFORE your passwords get touched inappropriately.

The problem is we hear about password theft much less frequently than we see something like this:

Those little dots are psychologically conditioning us. You can’t see the password, so it must be hidden. In fact, I’ve created passwords in my head that I have never actually seen in print. I haven’t documented these anywhere and they are long so they must be secure, right?

There is a small problem with Windows. The Security Account Manager (SAM) handles the passwords for Windows user accounts. As long as Windows is running this file can’t shouldn’t be allowed to be accessed, however Windows caches password hashes in memory. Once the password hashes are dumped they can be seen in plain text with a Google search.

Figure out how to dump the password hash you have never seen in plain text, then Google the hash. This is a great way to feel victimized, without actually being victimized. Of course someone could be monitoring your Google searches…

Thanks Volume Shadow Copy -> http://superuser.com/questions/364290/dump-sam-file-while-system-is-running

Or do it with fgdump -> http://www.foofus.net/~fizzgig/fgdump/downloads.htm

 

#sqlsat160 notes

Here are some personal notes I took for each session at SQLSaturday Kalamazoo 2012. They may or may not make sense to you but might come in handy for me later.

AlwaysOn

– Application Intent can go in the connection string to scale read operations to a secondary server
– An odd number of votes is needed in the cluster. Doesn’t have to be shared disk.
– Backups need some attention, Copy only and t-logs can be done on replicas
– statistics get stored in TempDB on secondaries
– RCSI probably not possible on primary
– Same service account
– Use PoSH for node voting
– Use Mgmt studio for AD object management not cluster manager
– GUI doesn’t turn on availability group… missing from script
– Make sure to enable Always on with SQL Config manager

Internals

– If SQL is slow its either Capacity, Configuration or Code
– Individual thread waits are show in os_waiting_tasks
– os_wait_stats are aggregated
– |———>|———->|————>
– wait_____signal______execute_________done
– 8KB pages include PageID, NextID, PrevID, OwningID..etc.etc
– traceon(3604) before DBCC Page(dbid, fileid, pageid)
– Bitmap pages are for optimization of allocation (0101101000) <—0's denote freespace
– GAM, SGAM, BCM, DCM, IAM
– Trace Flag 1118 mix extent use CMS and register the same server in a group multiple times to test concurrency
– Writelog waits are most common for log problems such as VLFs
– SleepTask waits could mean xml
– memory grants fall into buckets of queues divided by cost + memory

Trans Replication

– Can adjust retention of commands in the distribution database
– Distribution database can be on separate server from publisher
– can adjust agent retry settings
– sp_browsreplcmds can be helpful
– primary keys are required
– referential integrity just works, can be different RI on subscriber

Optimizer

– STMT -> Parsing -> Binding
– Goetz Graefe worked with Microsoft to help improve optimizer
– Trees of operators in query plans
– 8605 will give you a basic text plan
– query optimizer can use constraints (constant scan instead of table scan example)
– query optimizer is used to help with joining result sets in proper order
– there are sets of rules that can be turned on or off
– DBCC SHOWOFFRULES

Blogging

– sitemap google plugin
– writing order -> Madman -> Architect -> Carpenter -> Judge
– LiveWriter can help with posts and format