I hope someone here can assist me. I am trying to track down any potential hidden permissions that could give a windows user the ability to drop and create objects (stored procedures in this case, but I'm certain other objects are affected).
The group has db_datareader on the database. It is not a member of any server role except public. There are no user-created database roles or application roles. When I run the below code to figure out the object-level permissions, I get the results listed below the code. None of that looks like the ability to create or drop objects to me. To be sure I revoked ALTER on the group (even though I don't see it) and revoked VIEW DEFINITION. Again, nothing. Can someone help me figure this one out?
SELECT @@servername AS Servername, db_name() AS [Database], ObjName = convert(char(30), o.name),
ObjType = convert(char(100), o.type_desc),
UserName = convert(char(100),
case
when p.grantee_principal_id is null then ' (no users)'
when u.type = 'G' then lower(u.name)
else upper(u.name)
end),
Permission = (convert(char(100), p.permission_name + '/' + p.state_desc)),
getdate()
FROM sys.objects o
left join sys.database_permissions p on o.object_id = p.major_id
left join sys.database_principals u on p.grantee_principal_id = u.principal_id
WHERE db_name() = 'MyDB' AND p.permission_name IS NOT NULL
UNION
SELECT @@servername AS Servername, db_name() AS [Database], ObjName = convert(char(30), u.name),
ObjType = convert(char(100), p.class_desc),
UserName = convert(char(100),
case
when p.grantee_principal_id is null then ' (no users)'
when u.type = 'G' then lower(u.name)
else upper(u.name)
end),
Permission = (convert(char(100), p.permission_name + '/' + p.state_desc)),
getdate()
FROM sys.database_permissions p
left join sys.database_principals u
on p.grantee_principal_id = u.principal_id
WHERE db_name() = 'MyDB' AND p.permission_name IS NOT NULL
order by 5
ObjType UserName Permission
DATABASE MyUserGroup CONNECT/GRANT
DATABASE MyUserGroup EXECUTE/DENY
OBJECT_OR_COLUMN MyUserGroup EXECUTE/GRANT
SCHEMA MyUserGroup EXECUTE/GRANT
SCHEMA MyUserGroup REFERENCES/GRANT
SCHEMA MyUserGroup SELECT/GRANT
SCHEMA MyUserGroup VIEW DEFINITION/GRANT
SQL_STORED_PROCEDURE MyUserGroup EXECUTE/GRANT
SQL_STORED_PROCEDURE MyUserGroup EXECUTE/GRANT
SQL_STORED_PROCEDURE MyUserGroup EXECUTE/GRANT
SYSTEM_TABLE MyUserGroup REFERENCES/GRANT
SYSTEM_TABLE MyUserGroup SELECT/GRANT
SYSTEM_TABLE MyUserGroup VIEW DEFINITION/GRANT
August 20, 2021 at 9:09 pm
If the user owns a schema - then they will be able to create objects in that schema unless explicitly denied (and I am not sure if that can be denied). If the user is created without their own schema and assigned to a default schema that is owned by someone else (for example - dbo) then they only have access to the schema that is explicitly granted.
Are you seeing issues where someone is able to create something?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 21, 2021 at 9:33 am
There is no schema owned by this group. But you brought up an interesting point. Sometimes groups become owner of system created database schemas, so I double-checked. The group does not own dbo, db_owner or any of the other roles / schemas. They are creating procs in the dbo schema. And as far as I can tell, they are not nested (or part of) any other security group that has db_owner or sysadmin access.
August 21, 2021 at 12:12 pm
I assume you know which login dropped/created the procs.
and assuming you are a SYSADMIN what output do you get out of these
execute as login = 'loginname'
SELECT * FROM fn_my_permissions(NULL, 'SERVER');
use dbname_beingchanged
SELECT * FROM fn_my_permissions(NULL, 'Database');
revert
xp_logininfo @acctname = 'loginname', @option = 'all'
August 21, 2021 at 1:01 pm
Make sure to check not just that group's permission but every group in AD that that group might be a member of, nested groups are fun.
August 21, 2021 at 4:01 pm
I assume you know which login dropped/created the procs.
and assuming you are a SYSADMIN what output do you get out of these
execute as login = 'loginname' SELECT * FROM fn_my_permissions(NULL, 'SERVER');
use dbname_beingchanged SELECT * FROM fn_my_permissions(NULL, 'Database'); revert
xp_logininfo @acctname = 'loginname', @option = 'all'
4 of my favorite tools.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 23, 2021 at 4:28 pm
Another thing to check is linked servers. If a linked server connection connects to that instance as a user who has permission to drop and create objects AND you have RPC OUT turned on on that linked server, they can execute code to drop and create objects as the linked server user.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 23, 2021 at 4:32 pm
I assume you know which login dropped/created the procs.
and assuming you are a SYSADMIN what output do you get out of these
execute as login = 'loginname' SELECT * FROM fn_my_permissions(NULL, 'SERVER');
use dbname_beingchanged SELECT * FROM fn_my_permissions(NULL, 'Database'); revert
xp_logininfo @acctname = 'loginname', @option = 'all'
I do know who did it and thank you for this code. I will use it shortly for more investigation.
August 23, 2021 at 4:37 pm
Another thing to check is linked servers. If a linked server connection connects to that instance as a user who has permission to drop and create objects AND you have RPC OUT turned on on that linked server, they can execute code to drop and create objects as the linked server user.
It's definitely not a linked server issue. These users only connect directly to this instance and one shared her screen so I could verify the problem, and the instance name, etc.
August 23, 2021 at 7:04 pm
HMMM... We use groups in SQL Server, so the individual login isn't working. And the group gives the same error.
Msg 15406, Level 16, State 1, Line 1
Cannot execute as the server principal because the principal "login" does not exist, this type of principal cannot be impersonated, or you do not have permission.
Thoughts?
August 23, 2021 at 7:45 pm
which one failed?
and did you do
execute as login = 'domainname\username'
SELECT * FROM fn_my_permissions(NULL, 'SERVER');
i've used this with other windows groups only granted to sql and it works fine without the user having been directly granted access to either server/database
and make sure you do "revert" in any failure manually
August 25, 2021 at 4:16 pm
The individual users don't exist on the server. They are part of a windows login group, which is not returning results and erroring out.
I think I'm going nuclear. I've removed the group completely to test out their abilities and that will (I hope) let me flush out the problem as I slowly add permissions back in.
August 25, 2021 at 5:14 pm
Sent you a private message.
Well, THAT was a pain.
As Mr. Monk would say, "Here's what happened..."
Somehow, several members of one team (Team A) got added to the security group of another team (Team B). Team B has db_owner access in lower environments but not in production. Somewhere along the line, Team B ended up in the database level of security as db_owner even though they didn't have a server login (orphaned windows group). Despite being orphaned, the group retained the ability to do everything in the database.
The closest event I can think of that would have opened this up was a server migration where people were logging into the production server to resolve issues before it went live and one of us DBAs deleted the server level logins without checking the database level logins. I don't know for sure this was the event, but no one had issues before this event, so that's what I'm pinning the cause to.
SIGH. Literally the only way I figured this out was to stop looking at the server level stuff and start running queries on the local database. I found this link that led me to the solution...
August 25, 2021 at 5:43 pm
good.
that is almost the same script I was going to send you (with a few modifications of mine)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply