Login v. User v. Role v. Schema

Test post using WordPress on Droid.

Quite often these terms are bandied about interchangeably and therefore incorrectly. Perhaps it’s a good first full post for SQL N00B CREW training if we discuss the differences.

Login: a instance-level object that serves as the identity of the individual interacting with SQL Server.

User: the database-level identity object for a login.

Role: this is a collection of individual rights, either at a server or a database level. A login can then be assigned to a server-level role (such as Security Admins or Database Creators) or a user can be assigned to a database-level role (think db_creator or datareader) and have all the individual rights that roll up into that specific role.

Schema: when you hear schema think owner. Each object in a database (tables, views, stored procedures, etc.) have an owner; a schema. You can have multiple tables named Foo in a database, so long as they each are owned by different schemas.

Tying it all in…
You create a login on the SQL Server. You create a user from that login within a database. You assign the user to the db_owner database role and the user’s default schema to dbo.

But I’ll show you how to do that the next time I don’t type an entire post with my thumbs.