How to manage the IS_GRANTABLE security risk

Question:An internal security audit   returned a finding of  databases tables  set with IS_GRANTABLE = yes , indicating that any user with access can grant access to other users.  That is considered a security violation by most organisations. What measures can be implemented to mitigate the security risk?

Answer:According to MSDN Online “IS_GRANTABLE  Indicates whether the GRANTEE is permitted to grant permissions to other users (often referred to as "grant with grant" permission). Can be YES, NO, or NULL. An unknown, or NULL, value refers to a data source for which "grant with grant" is not applicable.”

How to view the IS_GRANTABLE status

One way to view the  IS_GRANTABLE status is sp_column_priveleges ‘my_table_name’

The IS_GRANTABLE flag is a default. And does allow permissions to be granted at any object level.  Consideration should be given to creating a policy for the security risk .  It is not uncommon for SQL Injections to occur via identifying the whether IS_GRANTABLE is set to YES or NO

 Some methods to manage the security risk

1)      Turn the IS_GRANTABLE flag to NO.  Before you change any flags , consider the consequences of changing the flag. 

2)      Run a regular security violation report which monitors for database users who have object level permissions.

3)       Database security is done with the use of AD groups or SQL Roles & Remove the use of  unauathorised elevated privileges

4)      Run a regular Database Server Security Audit

Read More on managing SQL Server Roles

How to Script database role permissions and securables

SQL Server - Find sql database users and Find database roles

SQL Database roles and members

SQL Server - Orphaned users and how to Synchronise Users and Logins after SQL Server 2005 restore

SQL Server - List all SQL Server users