The Dangers of Modifying the SQL Server Public Role

Introduction

Roles play a crucial part in managing permissions and access control. One such role that often gets overlooked is the public role. This role, by default, has minimal permissions and every database user is a member. Sometimes, however, there are permissions granted to the public role which puts your data at risk.

Understanding the Public Role

The public role is a built-in mechanism for granting base permissions to all users. By default, the public role has view access to all system views and tables, but it does not have any specific permissions to user tables, views, or stored procedures.

The Risks of Modifying the Public Role

Modifying the public role by adding extra permissions might seem like a quick fix when dealing with permission issues. However, doing so is fraught with danger. Since every user is a member of the public role, any permissions granted to this role are effectively granted to every user. As you can imagine, granting everyone the same level of permission across your SQL server instance is not something that should be done.

Real-World Consequences

Imagine a scenario where a well-meaning DBA grants insert permission on a specific table to the public role to save time. Now, every user, including those who should only have read access, can insert data into this table. This could lead to data integrity issues, incorrect reports, and potentially, a breach of sensitive information.

Best Practices for Managing SQL Server Roles

When managing SQL Server roles, it’s important to adhere to the principle of least privilege. This means granting only the minimum permissions necessary for a user to perform their job. Instead of modifying the public role, consider creating custom database roles with specific permissions and assigning users to these roles as needed.

Conclusion

Modifying the public role in SQL Server is a risky practice that can lead to serious security issues. It’s crucial to follow best practices for managing SQL Server roles to maintain the security and integrity of your databases. So, take a moment and use the scripts below to review your current explicitly granted permissions, and ensure you’re not inadvertently putting your data at risk by granting explicit permissions to the public role. As a bonus, the last two scripts will also tell you about any explicitly granted permissions for other users as well. Role based access control is a superior methodology for managing permissions, especially as the number of users to manage increases. For more information, please read my post on sql server roles “Role based access control in SQL Server, and you”. Feel free to review the scripts and take note of what I’ve excluded and modify them to meet your needs.

This first script identifies any explicitly granted permission to the instance role ‘public’

/* Script to identify explicitly granted permissions to the Public role*/
SELECT
SUSER_NAME(grantee_principal_id) grantee_name
, SUSER_NAME(grantor_principal_id) grantor_name
, class_desc
, CASE
	WHEN p.class = 100 THEN 'SERVER'
	WHEN p.class = 101 THEN pr.name
	WHEN p.class = 105 THEN e.NAME
	WHEN p.class = 108 THEN e.NAME
	end as [Securable]
, p.permission_name
, p.state_desc
FROM sys.server_permissions AS p
LEFT JOIN sys.endpoints e ON e.endpoint_id = p.major_id
LEFT JOIN sys.server_principals pr ON p.major_id = pr.principal_id

	WHERE p.type not in ('COSQ', 'CO', 'VWDB')
		AND SUSER_NAME(grantee_principal_id) =  'public'
		AND SUSER_NAME(grantee_principal_id) NOT LIKE '##%'
		AND SUSER_NAME(grantee_principal_id) NOT LIKE 'NT%'
		AND SUSER_NAME(grantor_principal_id) NOT LIKE '##%'
		AND SUSER_NAME(grantor_principal_id) NOT LIKE 'NT%'
		AND p.[state] IN ('G','W')

This second script identifies any explicitly granted permission to the database role ‘public’ on every database except the system databases:

/* explicit permissions granted to the Public role */
	IF OBJECT_ID('tempdb..#dbpublic') IS NOT NULL
    DROP TABLE #dbpublic

    CREATE TABLE #dbpublic (
        [DatabaseName] NVARCHAR(255)
        , [role] varchar(10)
		, [permission_name] NVARCHAR(100)
		, [object] NVARCHAR(256)
		, [type_desc] NVARCHAR(60)
        )

	DECLARE @DB_Name VARCHAR(256),
			@SQL NVARCHAR(3000);

	DECLARE public_cursor CURSOR FOR
		SELECT name 
		FROM master.sys.databases
		where database_id >4
		
	OPEN public_cursor 
	FETCH NEXT FROM public_cursor INTO @DB_Name 

	WHILE @@FETCH_STATUS = 0 
	BEGIN 

	SET @SQL = 'USE ' + QUOTENAME(@DB_Name) + '; ' +
	'INSERT INTO #dbpublic
	SELECT db_name() as [DatabaseName]
	, ''public'' AS role
	, per.permission_name 
	, CASE
	WHEN per.class = 0 THEN db_name()
	WHEN per.class = 1 THEN o.name
	WHEN per.class = 3 THEN schema_name(major_id)
	WHEN per.class = 4 THEN printarget.NAME
	WHEN per.class = 5 THEN asm.NAME
	WHEN per.class = 6 THEN type_name(major_id)
	WHEN per.class = 10 THEN xmlsc.NAME
	WHEN per.class = 15 THEN msgt.NAME COLLATE DATABASE_DEFAULT
	WHEN per.class = 16 THEN svcc.NAME COLLATE DATABASE_DEFAULT
	WHEN per.class = 17 THEN svcs.NAME COLLATE DATABASE_DEFAULT
	WHEN per.class = 18 THEN rsb.NAME COLLATE DATABASE_DEFAULT
	WHEN per.class = 19 THEN rts.NAME COLLATE DATABASE_DEFAULT
	WHEN per.class = 23 THEN ftc.NAME
	WHEN per.class = 24 THEN sym.NAME
	WHEN per.class = 25 THEN crt.NAME
	WHEN per.class = 26 THEN asym.NAME
	END as object
	, o.type_desc
	FROM sys.database_permissions AS per
	LEFT JOIN sys.database_principals AS prin ON per.grantee_principal_id = prin.principal_id
	LEFT JOIN sys.assemblies AS asm ON per.major_id = asm.assembly_id
	LEFT JOIN sys.xml_schema_collections AS xmlsc ON per.major_id = xmlsc.xml_collection_id
	LEFT JOIN sys.service_message_types AS msgt ON per.major_id = msgt.message_type_id
	LEFT JOIN sys.service_contracts AS svcc ON per.major_id = svcc.service_contract_id
	LEFT JOIN sys.services AS svcs ON per.major_id = svcs.service_id
	LEFT JOIN sys.remote_service_bindings AS rsb ON per.major_id = rsb.remote_service_binding_id
	LEFT JOIN sys.routes AS rts ON per.major_id = rts.route_id
	LEFT JOIN sys.database_principals AS printarget ON per.major_id = printarget.principal_id
	LEFT JOIN sys.symmetric_keys AS sym ON per.major_id = sym.symmetric_key_id
	LEFT JOIN sys.asymmetric_keys AS asym ON per.major_id = asym.asymmetric_key_id
	LEFT JOIN sys.certificates AS crt ON per.major_id = crt.certificate_id
	LEFT JOIN sys.fulltext_catalogs AS ftc ON per.major_id = ftc.fulltext_catalog_id
	LEFT JOIN sys.all_objects o ON o.object_id = per.major_id
	WHERE per.grantee_principal_id = DATABASE_PRINCIPAL_ID(''public'')
		AND [state] IN (''G'',''W'')
		AND NOT (
			per.class = 0
			AND prin.NAME = ''public''
			AND per.major_id = 0
			AND per.minor_id = 0
			AND permission_name IN (
				''VIEW ANY COLUMN ENCRYPTION KEY DEFINITION''
				,''VIEW ANY COLUMN MASTER KEY DEFINITION''
				)
			)
		--AND per.major_id NOT LIKE ''-%''
		AND NOT (
			o.is_ms_shipped = 1
			AND per.permission_name = ''SELECT''
			)
			'
		EXEC sp_executesql @SQL 

			/* iterate the cursor to the next database name */
			FETCH NEXT FROM public_cursor INTO @DB_Name 
		END 
		CLOSE public_cursor;
		DEALLOCATE public_cursor;


		Select * from #dbpublic

This third script identifies any explicitly granted permission to any other instance principals besides public:

-- Server Logins with explicitly granted permissions
SELECT
SUSER_NAME(grantee_principal_id) grantee_name
, SUSER_NAME(grantor_principal_id) grantor_name
, class_desc
, CASE
	WHEN p.class = 100 THEN 'SERVER'
	WHEN p.class = 101 THEN pr.name
	WHEN p.class = 105 THEN e.NAME
	WHEN p.class = 108 THEN e.NAME
	end as [Securable]
, p.permission_name
, p.state_desc
FROM sys.server_permissions AS p
LEFT JOIN sys.endpoints e ON e.endpoint_id = p.major_id
LEFT JOIN sys.server_principals pr ON p.major_id = pr.principal_id

	WHERE p.type not in ('COSQ', 'CO', 'VWDB')
		AND SUSER_NAME(grantee_principal_id) NOT LIKE 'public'
		AND SUSER_NAME(grantee_principal_id) NOT LIKE '##%'
		AND SUSER_NAME(grantee_principal_id) NOT LIKE 'NT%'
		AND SUSER_NAME(grantor_principal_id) NOT LIKE '##%'
		AND SUSER_NAME(grantor_principal_id) NOT LIKE 'NT%'
		AND p.[state] IN ('G','W')

This fourth and last script identifies explicitly granted permissions to any principals at the database level.

-- Database Logins with explicitly granted permissions

IF OBJECT_ID('tempdb..#dbperms') IS NOT NULL
    DROP TABLE #dbperms

    CREATE TABLE #dbperms (
        Database_Name NVARCHAR(255)
		, princpal_name NVARCHAR(100)
		, type_desc NVARCHAR(30)
        , state_desc NVARCHAR(30)
        , Permisison_name NVARCHAR(50)
        , object_name NVARCHAR(100)
        , obj_type_desc NVARCHAR(50)
        )

Insert #dbperms
EXEC sp_MSforeachdb 'USE ?
	SELECT DISTINCT db_name() as Database_name
	, pr.name AS princpal_name 
	, pr.type_desc
	, pe.state_desc
	, pe.permission_name
	, so.name as object_name
	, so.type_desc as obj_type_desc
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
    ON pe.grantee_principal_id = pr.principal_id
INNER JOIN sys.all_objects so
	ON pe.major_id = so.object_id
	WHERE pr.type IN (''U'',''G'', ''E'', ''X'', ''S'') 
	AND pr.name NOT LIKE ''%NT%'' 
	AND pr.name NOT LIKE ''%##%'' 
	AND permission_name not like ''%Connect%'''

select * from #dbperms