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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply