Category: Security

Jason Brimhall has an introductory-level post on server and database principals:

A fundamental component of SQL Server is the security layer. A principle player in security in SQL Server comes via principals. SQL Server principals come in more than flavor. (This is where a lot of confusion gets introduced.) The different types of principals are database and server. A database principal is also called a database user (sometimes just user). A server principal is also called a server login, or login for short.

Server and database principals are quite a bit different in function and come with different permission sets. The two are sometimes used, in reference, interchangeably, but that is done incorrectly. Today I hope to unravel some of what each is and how to see permissions for each type of principal.

Read the whole thing.

Comments closed

Ed Leighton-Dick on logins:

The first concept to understand about SQL Server’s security model is the difference between authentication and authorization.

• Authentication defines who is being given a right. SQL Server formally calls the authentication objects principals, but you’ll also see the older terms logins and users.

• Authorization defines what rights are being given. Formally, these are called permissions. In modern versions of SQL Server, permissions are very granular and can be found on nearly every object in the instance. There’s also a vast hierarchy that interrelates all of the permissions. (We’ll cover permissions in a future post. For now, know that they’re there.)

Ed has started a series on security basics.  Given that there are relatively few people who talk security (and even fewer who know security), I consider this a great thing.

Comments closed

Brent Ozar walks through one way to reduce SA account usage.

Part one:  the nice way.

In a perfect world, you’d create a minimally-privileged AD login that only has limited access to specific databases.

However, when you’ve got a toddler running with scissors and razors, sometimes you’re happy just to get the razors out of their hands first, and then you’ll work on the scissors next. One step at a time. Preferably not running.

For now, create another SQL account with DBO permissions on all of the databases involved with the application. (If you’re dealing with multiple different tenants on the same server, give them each their own SQL login.) Let them be complete owners of their databases for now.

Part two:  the not-as-nice way.

Power User: “EVERYTHING IS DOWN! THE SA ACCOUNT PASSWORD ISN’T WORKING! DID YOU RESET IT?”

Me: “Of course not. You told me not to.”

Power User: “THEN WHO DID IT?”

Me: “Oh, I have no way of knowing. Anyone who uses the account can change the password with theALTER LOGIN command. And you said everyone has it, right?”

That’s a nice account you have; it’d be a shame if something…unfortunate…were to happen to it.

Comments closed

The Microsoft ODBC Driver 13 is now available (in preview form):

The preview ODBC drivers for Linux now supports Ubuntu, RedHat and SUSE. This is Microsoft’s first ODBC Driver for SQL Server release supporting Ubuntu. You can now enjoy enterprise level support while connecting to SQL Server from Ubuntu. It also updates the drivers to unixODBC driver manager 2.3.1 support.

Full interoperability with distributions of Linux is something I’ve waited a long, long time for.  This is one tiny step closer.

Comments closed

Warner Chaves has a video introducing Always Encrypted:

This is the big difference of this new feature, that the operations to encrypt/decrypt happen on the client NOT on SQL Server. That means that if your SQL Server is compromised, the key pieces to reveal the data are NOT with the server. This means that even if your DBA wants to see the data, if they don’t have access to the CLIENT application then they won’t be able to see the values.

Always Encrypted strikes me as something that will be incredibly useful for 2-3% of the population, somewhat painful for 3-5% of the population, and completely ignored by the rest.  I’m currently on the fence about whether, three years from now, I will consider “completely ignored by the rest” to be a shame.

Comments closed

Andy Galbraith has a new permissions script:

I recently was tasked with this ticket:

Please add new login Domain\Bob to server MyServer.  Grant the login the same permissions as Domain\Mary.

On the face of it, this seems relatively straightforward, right?  It is the kind of request that we all get from time to time, whether as an ad-hoc task or as part of a larger project, such as a migration.

The catch of course is that it isn’t that easy – how do you know what permissions Mary has?

Andy’s script looks good.  For bonus points, compare it to fn_my_permissions.

Comments closed