Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Checking Permissions

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.

results1

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.

results2

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

Comments

Posted by cnewbou on 20 May 2011

I think that 0 values for major_id should be included in the query as they relate to database level permissions.

Posted by rharding on 20 May 2011

I ran your code and then did a Select OBJECT_NAME on the seven major_id values returned. The result was sp_helpdiagrams, sp_helpdiagramdefinition, sp_creatediagram, sp_renamediagram, sp_alterdiagram, sp_dropdiagram, and fn_diagramobject.

What should we do about these?

Posted by Steve Jones on 20 May 2011

Those are permissions for system procedures related to database diagrams. I would leave them alone.

Posted by Henry B. Stinson on 20 May 2011

SELECT  a.* ,

       name ,

       b.*

STYLE COMMENT: I don't like seeing the "name" field referenced without a table alias in front of it; plus, if name field is in a or b, then a.*, b.* will give name field anyway.  Or am I missing something here?

Posted by chumphrey 12211 on 20 May 2011

Am I missing something?  Neither query return a result set like the examples that are shown.  

Posted by Steve Jones on 20 May 2011

You might not have any permissions assigned to public.

Posted by SQL Server Youngling on 30 May 2011

Why

", b.permission_name, b.permission_name"

i.e. why repeat it?

Posted by jhenderson22 on 14 July 2011

So do you just blanket remove the permissions that have been granted to public (with exception to the diagram objects) or are you running a something (trace, maybe) to see if users are gaining access to the objects via the public role?

Leave a Comment

Please register or log in to leave a comment.