October 1, 2024 at 4:44 pm
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?
October 1, 2024 at 6:13 pm
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?
October 1, 2024 at 6:38 pm
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.
October 1, 2024 at 7:10 pm
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.
October 1, 2024 at 7:17 pm
Ah, that's good. I think.
Running that script for me shows no rights for dbo.
October 1, 2024 at 8:03 pm
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;
October 2, 2024 at 11:29 am
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
October 2, 2024 at 11:39 am
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.
October 4, 2024 at 2:05 pm
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.
October 4, 2024 at 2:15 pm
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply