Object-level permissions on dbo

  • I'm in a bit of a pickle. I had to remove all user permissions from a database but when a user tested to verify, they could still see everything. My first thought was that the public role had permissions on everything, but no, it's clean.

    Finally I noticed that the user dbo has object-level permissions on that database, where it doesn't have object-level permissions on another database that the user can't see anything in. Easy-peasy, I thought. I can just remove all the object-level permissions from dbo, which owns the db_owner role.

    Ummm. No. SQL Server just told me "Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself."

    How the heck am I supposed to fix this issue if I can't revoke what was obviously granted in some method or another? This is literally permissions on user-created objects and dbo has db_owner access anyway. Any thoughts?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Do you have a screenshot? Wondering what you've got here.

    I suspect logging in with the DAC might make this possible, but haven't tried. Curious what your system looks like. Can you script out the dbo user?

  • Script out of user just gives me this:

    /****** Object: User [dbo] Script Date: 10/1/2024 2:31:15 PM ******/

    CREATE USER [dbo] FOR LOGIN [sa] WITH DEFAULT_SCHEMA=[dbo]

    GO

    Using this script is where I see object level permissions:

    SELECT SDP.state_desc, SDP.permission_name, SSU.[name] AS RoleName, SSO.[name], SSO.[type],SSU.sid,sp.sid, sp.name,
    'REVOKE ' + SDP.Permission_name COLLATE SQL_Latin1_General_CP1_CI_AS
    + ' ON ' + SSO.name + ' TO dbo;'
    FROM sys.sysobjects SSO
    INNER JOIN sys.database_permissions SDP
    ON SSO.id = SDP.major_id
    INNER JOIN sys.sysusers SSU
    ON SSO.uid = SSU.uid
    INNER JOIN sys.database_principals sp
    ON SSU.sid = sp.sid
    ORDER BY SSU.[name], SSO.[name]

    Which returns a list similar to this:

    state_desc permission_name RoleName Objname type sid sid name (No column name)

    GRANT DELETE dbo Scrubbed U 0x01 0x01 dbo REVOKE DELETE ON Scrubbed TO dbo;

    GRANT INSERT dbo Scrubbed U 0x01 0x01 dbo REVOKE INSERT ON Scrubbed TO dbo;

    GRANT SELECT dbo Scrubbed U 0x01 0x01 dbo REVOKE SELECT ON Scrubbed TO dbo;

    GRANT UPDATE dbo Scrubbed U 0x01 0x01 dbo REVOKE UPDATE ON Scrubbed TO dbo;

    GRANT DELETE dbo Scrubbed U 0x01 0x01 dbo REVOKE DELETE ON Scrubbed TO dbo;

    GRANT INSERT dbo Scrubbed U 0x01 0x01 dbo REVOKE INSERT ON Scrubbed TO dbo;

    GRANT SELECT dbo Scrubbed U 0x01 0x01 dbo REVOKE SELECT ON Scrubbed TO dbo;

    GRANT UPDATE dbo Scrubbed U 0x01 0x01 dbo REVOKE UPDATE ON Scrubbed TO dbo;

    GRANT INSERT dbo Scrubbed U 0x01 0x01 dbo REVOKE INSERT ON Scrubbed TO dbo;

    GRANT REFERENCES dbo Scrubbed U 0x01 0x01 dbo REVOKE REFERENCES ON Scrubbed TO dbo;

    GRANT SELECT dbo Scrubbed U 0x01 0x01 dbo REVOKE SELECT ON Scrubbed TO dbo;

    GRANT UPDATE dbo Scrubbed U 0x01 0x01 dbo REVOKE UPDATE ON Scrubbed TO dbo;

    GRANT DELETE dbo Scrubbed U 0x01 0x01 dbo REVOKE DELETE ON Scrubbed TO dbo;

    GRANT INSERT dbo Scrubbed U 0x01 0x01 dbo REVOKE INSERT ON Scrubbed TO dbo;

    Here's the real kicker. I use an elevated permissions account to do my DBA work. So I added my non-privileged account to the server using tempdb as default, no server roles, no database users or permissions. Just added it as a base login. And that npa can read from both databases having the issue but no other database on this server.

    Public role turns up empty as far as securables go. Nothing in there. Both the GUI and the code (select schema_name(major_id) schema_name, permission_name, state_desc

    from sys.database_permissions p

    where grantee_principal_id = user_id('Public')

    and class_desc = 'SCHEMA') agree.

    I'm lost. I have no idea how those object-level perms got on dbo to begin with. And as an FYI, dbo user is mapped server login sa, which is disabled.

    Attachments:
    You must be logged in to view attached files.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Oh, good grief. My check on public securables finally loaded (I had turned away from it and forgotten to close it after taking that screenshot). Apparently it is public, not dbo. It was just taking too long to load.

    And that first script, I cribbed it from a StackOverflow article. Apparently it's not exactly correct if it was showing public stuff as being owned by the dbo user.

    I feel so annoyed at myself now.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Ah, that's good. I think.

    Running that script for me shows no rights for dbo.

     

  • I'm assuming that it only showed for dbo because it was in public. Because it didn't show for dbo on other databases.

    The script I should have used is below:

     

    SELECT DISTINCT rp.name, 
                    ObjectType = rp.type_desc, 
                    PermissionType = pm.class_desc, 
                    pm.permission_name, 
                    pm.state_desc, 
                    ObjectType = CASE 
                                   WHEN obj.type_desc IS NULL 
                                         OR obj.type_desc = 'SYSTEM_TABLE' THEN 
                                   pm.class_desc 
                                   ELSE obj.type_desc 
                                 END, 
                    s.Name as SchemaName,
                    [ObjectName] = Isnull(ss.name, Object_name(pm.major_id)) ,
    'REVOKE ' + pm.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + ' ON ' 
    + s.name + '.' + Isnull(ss.name, Object_name(pm.major_id)) + ' TO ' + rp.name + ';'
    FROM   sys.database_principals rp 
           INNER JOIN sys.database_permissions pm 
                   ON pm.grantee_principal_id = rp.principal_id 
           LEFT JOIN sys.schemas ss 
                  ON pm.major_id = ss.schema_id 
           LEFT JOIN sys.objects obj 
                  ON pm.[major_id] = obj.[object_id] 
           LEFT JOIN sys.schemas s
                  ON s.schema_id = obj.schema_id
    WHERE  rp.type_desc = 'DATABASE_ROLE' 
           AND pm.class_desc <> 'DATABASE' 
       AND 'REVOKE ' + pm.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + ' ON ' 
    + s.name + '.' + Isnull(ss.name, Object_name(pm.major_id)) + ' TO ' + rp.name IS NOT NULL
    ORDER  BY rp.name, 
              rp.type_desc, 
              pm.class_desc;

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • did you check for "Cross database ownership chaining" ?

     

    Ref 2: "Understanding Cross Database Ownership Chaining in SQL Server"

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • No, it was definately object-level permissions on the public role. I revoked them and my regular account no longer could see the database data.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Following up on this... Will it hurt if I remove system object permissions from the public role?

    Or, alternatively, is there a method to ensure that I only remove user objects from the public role in multiple databases without having to add each one to a <> WHERE clause filter?

    I'm trying to automate this but I'm worried about removing system object permissions from the public role.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Oh, nevermind. I think I found it. WHERE sys.objects.object_id > 0 AND sys.objects.object_id < 1042102753.

    Let me know if anyone knows of any other method.

    EDIT: Hrm. Nope. Not quite the solution I had in mind. That 104 number comes from the system diagram tables in 2017 but it appears objects can be created past that number, and it doesn't cover 2022, which doesn't help me.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply