locked
sys.database_permissions major_id = sys.sysallocunits versus sys.sysrowsets RRS feed

  • Question

  • Hi,

    I was hoping you could help me.  I created a SQL login on two servers and GRANTed them SELECT permissions on a schema.  When I run a permissions query (using the function entity_instance_name from the web - don't remember where but THANKS!! to the writer)  against the logins the major_id of sys.databasepermissions differs between the two instances (2008 R2).  The first one was created through SSMS and the second was created by batch script sqlcmd.

    SSMS:

    Permission            Class_desc        object_name(major_id)           Function column returns SCHEMA NAME

    GRANT SELECT     SCHEMA            sysallocunits                            Schema_Name

    From .bat SQLCMD:

    GRANT SELECT     SCHEMA             sysrowsets                              Schema_Name

    That is the only difference between the instances and the process I can think of.  Does anyone know if this affects any permissions?  Please let me know if you need any more information.

    thanks for the time,

    Mike

    Thursday, May 15, 2014 4:40 PM

Answers

  • The query you are using must be looking for permissions on a table, instead of a query. SELECT name, object_id FROM sys.objects WHERE object_id < 15; will return the tables you refer to (sysallocunits and sysrowsets). But you not looking for permissions on tables. You want permissions on schemas.

    SELECT * FROM sys.schemas; will return the schema_id of the schemas. The major_id in SELECT * FROM sys.database_permissions; depends upon the type of object you are looking at. For a table you join to sys.tables (or sys.objects) but for a schema you have to join to sys.schema. (For a certificate you join to sys.certificates, etc.)

    And the id number of any object in one database (or instance) won't necessarily match any other database (or instance). Execute CREATE SCHEMA A; then CREATE SCHEMA B; in one database, and then just execute CREATE SCHEMA B; in another database will get you two different id numbers, because they are numbered sequentially, and you have different histories in the two databases.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, May 19, 2014 4:08 PM

All replies

  • I don't know exactly where this object_id(major_id) came from, but since major_id in case of a schema-level permission, it does not make much sense.

    The object id for sysallocunits is 7 while the object id for sysrowsets is 5, so maybe it is the same schema on the two servers, but the schema has different ids. Or they are completely different schemas.

    Try schema_name(major_id) instead.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, May 15, 2014 9:31 PM
  • Thanks for the reply.  The permissions are set at the schema scope.  So the "Schema_Name" is the same on both servers.  This question is the result of a migration of DDL from one server to another.  I've also noticed this on other accounts where this field has sysfiles1 instead of sysallocunits or sysrowsets???

    thanks for the time

    Monday, May 19, 2014 2:47 PM
  • The query you are using must be looking for permissions on a table, instead of a query. SELECT name, object_id FROM sys.objects WHERE object_id < 15; will return the tables you refer to (sysallocunits and sysrowsets). But you not looking for permissions on tables. You want permissions on schemas.

    SELECT * FROM sys.schemas; will return the schema_id of the schemas. The major_id in SELECT * FROM sys.database_permissions; depends upon the type of object you are looking at. For a table you join to sys.tables (or sys.objects) but for a schema you have to join to sys.schema. (For a certificate you join to sys.certificates, etc.)

    And the id number of any object in one database (or instance) won't necessarily match any other database (or instance). Execute CREATE SCHEMA A; then CREATE SCHEMA B; in one database, and then just execute CREATE SCHEMA B; in another database will get you two different id numbers, because they are numbered sequentially, and you have different histories in the two databases.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, May 19, 2014 4:08 PM