SELECT 
	   u.name AS [Principal]
	 , o.name AS [Object]
	 , p.permission_name AS [Permission]
	 , p.state_desc AS [State]
	 , schema_name(o.schema_id) AS [SchemaName]
	 , u.principal_id
FROM sys.database_principals u 
INNER JOIN sys.database_permissions p ON u.principal_id = p.grantee_principal_id 
INNER JOIN sys.all_objects o ON p.major_id = o.object_id
WHERE 1 = 1
AND p.state_desc <> 'DENY'
AND u.name IN ('guest', 'public');

USE [master] REVOKE SELECT ON INFORMATION_SCHEMA.[CHECK_CONSTRAINTS] TO public;
USE [master] REVOKE SELECT ON INFORMATION_SCHEMA.[COLUMN_DOMAIN_USAGE] TO public;
--etc.

USE <UserDatabase>
GO

SELECT *
FROM [INFORMATION_SCHEMA].[CHECK_CONSTRAINTS];

USE YourDatabase; DENY SELECT ON sys.[check_constraints] TO public;
-- note that this affects all users in all databases who only have public membership into master, i.e. mapped via the guest user
USE master; DENY SELECT ON INFORMATION_SCHEMA.check_constraints TO public;

USE YourDatabase; REVOKE SELECT ON sys.[check_constraints] TO public;
USE master; GRANT SELECT ON INFORMATION_SCHEMA.check_constraints TO public;

--create a test user in our specific database.
CREATE USER [ClarkKent] WITHOUT LOGIN--make sure I'm in the correct database

--test:
EXECUTE AS USER='ClarkKent'
--who am i?
select suser_name()

--what kind of permissions do i have?
--for 2008 and above:
  --the auditing snippet below works fine in a 
  --login trigger, 
  --database trigger 
  --or any stored procedure.
  SELECT 
    getdate()                                    AS EventDate,
    DB_NAME()                                    AS DBName,
    CURRENT_USER                                 AS CurrentUser,
    HOST_NAME()                                  AS HostName,
    APP_NAME()                                   AS ApplicationName,
    OBJECT_NAME(@@PROCID)                        AS ProcedureName,
    USER_ID()                                    AS Userid,
    USER_NAME()                                  AS UserName,
    SUSER_ID()                                   AS sUserid,
    SUSER_SNAME()                                AS sUserName,
    IS_SRVROLEMEMBER ('sysadmin')                AS [Is_ServerAdmin_Sysadmin],
    IS_MEMBER('db_owner')                        AS [Is_DB_owner],
    IS_MEMBER('db_ddladmin')                     AS [Is_DDL_Admin],
    IS_MEMBER('db_datareader')                   AS [Is_DB_Datareader],
    ORIGINAL_LOGIN()                             AS [ORIGINAL_LOGIN],
    --these params were introduced in SQl2008
    ConnectionProperty('net_transport')          AS 'net_transport', 
    ConnectionProperty('protocol_type')          AS 'protocol_type',
    ConnectionProperty('auth_scheme')            AS 'auth_scheme',
    ConnectionProperty('local_net_address')      AS 'local_net_address',
    ConnectionProperty('local_tcp_port')         AS 'local_tcp_port',
    ConnectionProperty('client_net_address')     AS 'client_net_address',
    ConnectionProperty('physical_net_transport') AS 'physical_net_transport'
--this database permissions?
SELECT
  *
FROM       sys.database_permissions permz
INNER JOIN sys.database_principals userz
  ON permz.grantee_principal_id = userz.principal_id
WHERE      userz.principal_id IN( --the userid and also all the roles i'm directly in.
                                  --that doesn't seem to get the role-within-a-role info those
                                 SELECT
                                   USER_ID() AS id
                                 UNION ALL
                                 SELECT
                                   rolezx.role_principal_id
                                 FROM            sys.database_principals userzx
                                 LEFT OUTER JOIN sys.database_role_members rolezx
                                   ON userzx.principal_id = rolezx.member_principal_id
                                 LEFT OUTER JOIN sys.database_principals decripz
                                   ON rolezx.role_principal_id = decripz.principal_id
                                 WHERE           userzx.name = USER_NAME()) 
--do stuff


--change back into superman
REVERT;

USE [master];
CREATE LOGIN [ClarkKent2] WITH PASSWORD=N'<SomeSecurePassword>';

USE [MyDb];
CREATE USER [ClarkKent2] FOR LOGIN [ClarkKent2];

USE MyDb;

SELECT *
FROM [INFORMATION_SCHEMA].[CHECK_CONSTRAINTS];