SQL Server Permission Approaches

Various approaches to managing permissions are available, depending on your application model and security needs. Except for databases with few users, you should try to manage permissions based on roles or groups. Managing permissions on a user-by- user basis is hard to keep track of and invariably leads to application problems and security issues.

Granting Permissions to Public

As all users are members of public, the simplest way to assign permissions common to all users is to assign them to public. This is usually only appropriate in development or on low security databases. Remember that if a guest account exists, then everyone with a SQL Server login will also be assigned any permissions granted to public. To DENY a permission to public denies all users.

Granting Permissions to User-Defined Roles

A common and flexible way to manage permissions is to create roles, assign the permissions to the roles, and then assign users to the roles. As roles can be assigned to roles as well, permissions can be nested, creating a module design.

Granting Permissions Using Views

Views can be used as a security layer between the users and the data. Rather than giving direct access to the tables, creating views and then assigning permissions to the views is another common practice. This also allows changes to the underlying tables without affecting the application, as long as the views present the same data to the application.

Granting Permissions Using Stored Procedures

Managing permissions through stored procedures works much the same way as through views. No permissions are assigned on the database objects ”only on the stored procedures. This way, all access to the data must be through the procedures; additional functionality such as error testing can then be built into the stored procedures to protect the data.

Granting Permissions Using Application Roles

As discussed earlier, application roles protect the data by requiring a password, embedded in the application, to be handed over before the role's permissions are applied. This way, the only way a user can access the data is through the application, which can have various levels of protection built into it.