Understanding Database Role Permissions

Jason Brimhall shows what happens when you make a user a member of every database role at the same time:

A fundamental component of SQL Server is the security layer. A principle player in security in SQL Server comes via principals. In a previous article, I outlined the different flavors of principals while focusing primarily on the users and logins. You can brush up on that article here. While I touched lightly, in that article, on the concept of roles, I will expound on the roles a bit more here – but primarily in the scope of the effects on user permissions due to membership in various default roles.

Let’s reset back to the driving issue in the introduction. Frequently, I see what I would call a gross misunderstanding of permissions by way of how people assign permissions and role membership within SQL Server. The assignment of role membership does not stop with database roles. Rather it is usually combined with a mis-configuration of the server role memberships as well. This misunderstanding can really be broken down into one of the following errors:

  • The belief that a login cannot access a database unless added specifically to the database.

  • The belief that a login must be added to every database role.

  • The belief that a login must be added to the sysadmin role to access resources in a database.

Worth reading.  Spoilers:  database roles are not like Voltron; they don’t get stronger when you put them all together.

Related Posts

Dealing With Noisy Neighbors

Kevin Kline explains what Resource Governor does: There are lots of ways to manage noisy neighbors. For example, you could spin up additional instances of SQL Server on a single physical or virtual machine (VM), and then segregate the applications to a distinct instance. You could also follow the old adage of “one application, one […]

Read More

Creating Database Snapshots

David Fowler has a script which lets you create database snapshots easily: The procedure takes two parameters, @DatabaseList – a comma delimited string of database names, allows wildcards @ListOnly – 1- a list of affected databases will be displayed but snapshots aren’t created. 0- Snapshots are created automatically DEFAULT I’m a big fan of database […]

Read More

Categories

March 2017
MTWTFSS
« Feb Apr »
 12345
6789101112
13141516171819
20212223242526
2728293031