Someone posted this query recently:

select a.*,name, b.*
from sys.database_principals a, sys.database_permissions b

where permission_name = 'INSERT'
and
b.grantee_principal_id = a.principal_id

That’s a little ugly, so let’s fix it:

SELECT  a.name, a.principal_id, a.is_fixed_role
      , a.default_schema_name
      , b.permission_name, b.permission_name
      , b.state_desc
FROM    sys.database_principals a
  INNER JOIN sys.database_permissions b
  ON b.grantee_principal_id = a.principal_id
WHERE   a.name = 'public'
        

If you run this, you’ll get INSERT permissions in your database. In this case, the person had one row returned that had “public” in it, as shown.

I normally don’t have permissions for public, but in this case I had run this first:

GRANT INSERT ON Person.Address TO Public

I don’t recommend permissions for public, and you really ought to run this on all your servers:

SELECT  a.name, a.principal_id, a.is_fixed_role
      , a.default_schema_name
      , b.permission_name, b.permission_name
      , b.state_desc
FROM    sys.database_principals a
  INNER JOIN sys.database_permissions b
  ON b.grantee_principal_id = a.principal_id
WHERE   a.name = 'public'
AND b.major_id > 0

How do you find out which objects have permissions? There’s a clue in the last query. If you scroll across in the results, there’s a major_id column. You can use that to find the object.

The OBJECT_NAME function is handy here, and it takes an object_id, which is the major_id. If I run this:

SELECT OBJECT_NAME(85575343)

I get “Address” back, which is the object I altered.

And, of course, we need to clean up

REVOKE INSERT ON Person.Address TO Public

Filed under: Blog Tagged: security, syndicated, T-SQL