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 permission_name = 'INSERT'
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 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
