SQL Server Best Practices: Use Roles When You Can

SQL Server has two major security vectors: “Principals”, which are primarily users and roles (groups), and “Securables”, which are primarily objects on the server or in the database, like tables or views. Many applications use Logins for their users, and then tie those Instance Logins to Database Users. The Database Users are then given rights and permissions to database objects like tables or stored procedures.

If you can, it’s a good idea to apply permissions not to the individual users, but to database roles. The reason is that you can move users in and out of the roles without changing the permission scheme.

It also helps if you want to transfer the database to another server. All you have to do is script out the groups and permissions, and when you transfer the database to another system you simply add the users on that system to the proper roles – no permission changes needed.

Skip to main content