Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Basit's SQL Server Tips

Basit Farooq is a Lead Database Administrator, Trainer and Technical Author. He has more than a decade of IT experience in development, technical training and database administration on Microsoft SQL Server platforms. Basit has authored numerous SQL Server technical articles, and developed and implemented many successful database infrastructure, data warehouse and business intelligence projects. He holds a master's degree in computer science from London Metropolitan University, and industry standard certifications from Microsoft, Sun, Cisco, Brainbench, Prosoft and APM, including MCITP Database Administrator 2008, MCITP Database Administrator 2005, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

The PUBLIC role – Do not use it for database access!

01As per Microsoft Books Online and SQL Server Security best practice white paper, it is recommended to periodically review privileges granted to public role, and revoke any unnecessary privileges assigned to this role. This is because public role is a special database role that exists in every user database, and by default, every database user is automatically assigned to this built-in role. This role is similar to Windows NT Everyone group, for example, if you grant privileges to this role, then all members of this role automatically get’s this permission. Due to this reason, when locking down access controls, then, we need to look at each individual user’s privileges as well as the privileges assigned to public role.

You can use the following query, which lists all privileges that has been granted to public database role in the specified database:

USE [<Database_Name>] -- Specify database name
GO

WITH [PublicRoleDBPermissions]
AS
(SELECT p.[state_desc] AS [PermissionType]
       ,p.[permission_name] AS [PermissionName]
       ,USER_NAME(p.[grantee_principal_id]) AS [DatabaseRole]
       ,CASE p.[class]
          WHEN 0 THEN 'Database::' + DB_NAME()
          WHEN 1 THEN OBJECT_NAME(major_id)
          WHEN 3 THEN 'Schema::' + SCHEMA_NAME(p.[major_id]) 
        END AS [ObjectName]
 FROM [sys].[database_permissions] p
 WHERE p.[class] IN (0, 1, 3)
        AND p.[minor_id] = 0)
SELECT [PermissionType]
      ,[PermissionName]
      ,[DatabaseRole]
      ,SCHEMA_NAME(o.[schema_id]) AS [ObjectSchema]
      ,[ObjectName]
      ,o.[type_desc] AS [ObjectType]
      ,[PermissionType] 
            + ' ' + [PermissionName] 
            + ' ON ' + QUOTENAME(SCHEMA_NAME(o.[schema_id])) 
            + '.' + QUOTENAME([ObjectName]) 
            + ' TO ' + QUOTENAME([DatabaseRole]) AS [GrantPermissionTSQL]
      ,'REVOKE'
            + ' ' + [PermissionName] 
            + ' ON ' + QUOTENAME(SCHEMA_NAME(o.[schema_id])) 
            + '.' + QUOTENAME([ObjectName]) 
            + ' TO ' + QUOTENAME([DatabaseRole]) AS [RevokePermissionTSQL]
FROM [PublicRoleDBPermissions] p
JOIN [sys].[objects] o
    ON o.[name] = p.[ObjectName]
AND OBJECTPROPERTY(o.object_id, 'IsMSShipped') = 0
WHERE [DatabaseRole] = 'Public'
ORDER BY [DatabaseRole], [ObjectName], [ObjectType]

This query is written using following two system catalogs: sys.database_permissions and sys.objects, and returns the following columns:

  • PermissionType – Returns the type of granted permission.
  • PermissionName – Returns the name of permission.
  • DatabaseRole – Returns the public role name.
  • ObjectSchema – Returns the schema of database securable, to which the securable belongs.
  • ObjectName – Returns the name of the database securable.
  • ObjectType – Returns the type of database object.
  • GrantPermissionTSQL – Returns the statements to grant public database role access on all database objects. Note: The results of this statement are used for rollback purposes.
  • RevokePermissionTSQL – Returns statements to revoke public database role access from all database objects, where it has permissions.

Sample Ouput

img01

img02

If this query returns rows, this means that unnecessary privileges have been granted to public role. To remove these unnecessary privileges, create a user defined database role, and grant this user define database role same permissions that has been granted to public role. Once done, add required database users to this role, and after that, revoke the permissions assigned to public database roles.

For more information about SQL Server Server-Level and Database-Level roles, see Server-Level Roles and Database-Level Roles.


Comments

Leave a comment on the original post [basitaalishan.com, opens in a new window]

Loading comments...