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.