locked
excessive permissions when viewing fn_my_permissions RRS feed

  • Question

  • hi all,

    yesterday, I removed db_owner permission from a bunch of developers in production and replaced it with db_datareader.  I thought that was the end of it.  One of my developer this morning told me that he could still perform update operations against two databases.  This developer is part of an active directory security group.

    I ran the following to check things out:

    EXECUTE AS USER = 'domain\username'
    GO
    --SELECT USER_NAME()
    go
    SELECT * FROM fn_my_permissions (NULL, 'DATABASE'); 
    GO
    REVERT

    The following is the output:

    I have executed revoke all from the user and the AD group.  that didn't make a different.  I have checked everything I can think off to try and remove this set of permissions down to just connect and select.  I have been unsuccessful. 

    Does anyone know where else I can check to see where these permissions are coming from?

    thanks.

    Tuesday, September 6, 2016 8:19 PM

Answers

  • I fixed the URL from Rick reply. The tool incorrectly interpreted the "." after the URL as part of the URL. Just in case I am pasting it again:

    http://social.technet.microsoft.com/wiki/contents/articles/15180.effective-database-engine-permissions.aspx

    BTW. I also noticed that The token includes a group named "***\WebServer Application Accounts", from the set of role memberships you included, it seems like the user may be getting membership on "db_executor" & "db_writer"  through this particular account (although I cannot tell for sure as the name is obfuscated). I would recommend double-checking this particular role membership & see if my guess is correct.

    I Hope this helps.

    Thanks,

    -Raul Garcia

     SQL Security


    This posting is provided "AS IS" with no warranties, and confers no rights.

    • Marked as answer by Red8Rain Wednesday, September 7, 2016 9:04 PM
    Wednesday, September 7, 2016 5:01 PM

All replies

  • What does
    SELECT * FROM sys.user_token

    return? This returns all active tokens for the user.

    Tuesday, September 6, 2016 9:12 PM
  • record 1 is the user i'm executing as when I ran the select statement.

    record 3 is the AD group that had dbo access until yesterday, it now "should" only have read.

    Tuesday, September 6, 2016 9:34 PM
  • Given that the token is also showing role membership for db_datawriter & db_executor roles, I would recommend running the following query:

    SELECT user_name(role_principal_id) as [role], user_name(member_principal_id) as [member] FROM sys.database_role_members WHERE role_principal_id in ( database_principal_id('db_datawriter'), database_principal_id('db_executor') , database_principal_id('db_owner'))

    I hope this helps.

    -Raul Garcia

     SQL Security


    This posting is provided "AS IS" with no warranties, and confers no rights.

    Tuesday, September 6, 2016 10:20 PM
  • All these tokens contribute to the users' permissions. We can see that the user has membership directly or indirectly in db_datawriter. If the highlighted role only has membership db_datareader, this would imply that membership comes from the role db_executor or the Windows group Web Server Application Accounts.

    Wednesday, September 7, 2016 7:24 AM
  • that's the problem.  the user can only access via the AD group and the AD group doesn't have db_datawriter or db_executor permission.

    here's the output from the query:

    SELECT user_name(role_principal_id) as [role], user_name(member_principal_id) as [member] FROM sys.database_role_members WHERE role_principal_id in ( database_principal_id('db_datawriter'), database_principal_id('db_executor') , database_principal_id('db_owner'))

    here is a screenshot of the db_datawriter role at the database's security hive:

    as you can see, the AD group named: grpSOEP-dbo doesn't exists in any of them.  The user account has access to this instance and database via grpSOEP-dbo.

    Wednesday, September 7, 2016 2:51 PM
  • You may be interested in the information and scripts that I have posted to Database Engine Effective Permissions http://social.technet.microsoft.com/wiki/contents/articles/15180.effective-database-engine-permissions.aspx. You have certainly already checked some of these things.

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


    Wednesday, September 7, 2016 3:39 PM
  • Hi Rick,

    getting an error, when trying to access the URL you provided.

    Server Error in '/wiki' Application.

    The resource cannot be found.

    Wednesday, September 7, 2016 4:46 PM
  • I fixed the URL from Rick reply. The tool incorrectly interpreted the "." after the URL as part of the URL. Just in case I am pasting it again:

    http://social.technet.microsoft.com/wiki/contents/articles/15180.effective-database-engine-permissions.aspx

    BTW. I also noticed that The token includes a group named "***\WebServer Application Accounts", from the set of role memberships you included, it seems like the user may be getting membership on "db_executor" & "db_writer"  through this particular account (although I cannot tell for sure as the name is obfuscated). I would recommend double-checking this particular role membership & see if my guess is correct.

    I Hope this helps.

    Thanks,

    -Raul Garcia

     SQL Security


    This posting is provided "AS IS" with no warranties, and confers no rights.

    • Marked as answer by Red8Rain Wednesday, September 7, 2016 9:04 PM
    Wednesday, September 7, 2016 5:01 PM
  • good eye Raul.  I saw that but thought it was a service account. Just removed the webserver Application accounts from the sql instance.

    Thanks for helping me catch it!

    Wednesday, September 7, 2016 9:05 PM