Hi Kim KK,
Please see the details below:
“Public” Role in SQL Server is a unique role given by default to all logins. You cannot remove a login's membership of this role because this behavior is built into SQL Server.
NOTE:
- Please be sure to document it before revoking the server level and database level permission.
- Get the list verified by the security team to test by revoking the permission.
Additional details:
When a SQL Server login is created, the public role is assigned to the login and cannot be revoked. After creating the login, if that server principal is not granted or denied specific permissions on any securable object, the login will automatically inherit the permissions granted to the public role.
--Server level
SELECT sp.state_desc as "Permission State", sp.permission_name as "Permission",
sl.name "Principal Name",sp.class_desc AS "Object Class", ep.name "End Point"
FROM sys.server_permissions AS sp
JOIN sys.server_principals AS sl
ON sp.grantee_principal_id = sl.principal_id
LEFT JOIN sys.endpoints AS ep
ON sp.major_id = ep.endpoint_id WHERE sl.name = 'public';
--database level
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 and USER_NAME(p.[grantee_principal_id])='public'
Sample code to revoke the SELECT permission
REVOKE SELECT on sys.columns TO public
