CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2008-r2sql-grant
Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5787

System objects return wrong GRANTs

I found strange bug or behaviour on our SQL Server 2008R. I am trying to give EXECUTE permissions to a role for 1 schema. However when I am looking to the system objects I see that EXECUTE permissions are set to the SYSTEM_TABLE sysallocunits. If I give permissions to any other schema then everything goes well and the permissions are set to the schema. I use following query

GRANT EXECUTE ON SCHEMA::Sync TO [app_TSSyncService_web_svc]

select  princ.name 
,       princ.type_desc 
,       perm.permission_name 
,       perm.state_desc 
,       perm.class_desc 
,       object_name(perm.major_id) 
from    sys.database_principals princ 
left join 
        sys.database_permissions perm 
on      perm.grantee_principal_id = princ.principal_id 
WHERE princ.name = 'app_TSSyncService_web_svc'

I am getting following output:

name    type_desc   permission_name state_desc  class_desc  (No column name)
app_TSSyncService_web_svc   DATABASE_ROLE   EXECUTE GRANT   SCHEMA  sysallocunits

The same thing happens when I grant permissions to this schema for any other role.

UPDATE Just found out that my schema has the same object_id sysallocunits any suggestions how can I rewrite query to ignore it without hardcoding?

Upvotes: 0

Views: 270

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

No, you schema has a schema_id that happens to have the same numeric value as the object_id for sysallocunits.

You query is wrong for checking permissions for anything that isn't an object; for a schema, you'd want to join major_id from sys.database_permissions to the schema_id in sys.schemas, rather than assuming that it's an object_id to join to sys.objects.

I'm not going to attempt to completely re-write your query here since I don't know what class(es) you'll want to be able to query permissions for.

Upvotes: 2

Related Questions