Post reply

Hiding the security folder from contractors using SMSS

  • August 19, 2015 at 3:55 am

    #319971

    Dear Everyone

    I am setting up a database for one contractor who needs to access one of our databases but i need to remove the permission for them to view the security folder so he cant access the SA account and enable it. I was able to deny them the view permission to see all the other databases and hide the system objects but i can still see the security folder and the sa account there where i can disable and enable it as i please.

    Has anyone been able to disable permission to this folder?

    regards

    K.

  • August 19, 2015 at 6:41 am

    #1821012

    you are worried about something you don't need to worry about.

    simply make sure they are not sysadmins.

    if you create a user for your contractor, the ONLY user they can see are themselves and sa. no other sysadmins, no other regular users.

    since they are not sysadmins on the instance, they cannot make any changes to sa no matter what they see.

    you can test this yourself, for piece of mind.

    on my server, i have dozens and dozens of users and windows groups that exist as logins.

    i created a user, and opened SSMS as that user.

    i can see only myself, and sa. prove it to yourself, even if you can see database names or the sa user, you cannot modify them, unless permissions were granted,.

    create login ClarkKent with password = 'NotTheRealPassword!'

    then i logged in with those credentials.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 19, 2015 at 8:57 am

    #1821085

    Just make sure the contractor has access to only the specific database they need. They can't grant themselves role membership or permissions at server level or another database, unless you put them in sysadmin server level role.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • August 19, 2015 at 9:50 am

    #1821108

    I don't believe you can hide this folder.

  • August 19, 2015 at 10:06 am

    #1821112

    Not unless there is a SSMS AddIn out there to hide or disable certain menu options and object explorer details. When you consider all the sysadmin related functionality that's in SSMS, I can see how some shops may want to hide some of the non-developer related features, not for security sake, becuase the features arn't functional without sysadmin or DBO membership, but just for reducing clutter and ease of use. For example: QA, data analysts, or BI team members only need to do stuff like explore objects, view schema, query data, and maybe examine contents of stored procedures. There are probably better query oriented tools, but SSMS is the defacto go to tool, so it gets a broad range of end users. Actually, SSMS itself could do a better job of graying out features for which the current login does not have permission, it would be best for the SSMS dev team to bake that into the tool.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • August 19, 2015 at 11:51 pm

    #1821259

    hurricaneDBA (8/19/2015)

    Dear Everyone

    I am setting up a database for one contractor who needs to access one of our databases but i need to remove the permission for them to view the security folder so he cant access the SA account and enable it. I was able to deny them the view permission to see all the other databases and hide the system objects but i can still see the security folder and the sa account there where i can disable and enable it as i please.

    Has anyone been able to disable permission to this folder?

    regards

    K.

    The contractor only needs "dbo" privs on the database they're working on. Simply grant then only that priv and don't grant him sysadmin privs. If he needs to do something that would require sysadmin privs, then you do it for him.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 20, 2015 at 6:38 am

    #1821364

    Thanks for the reply everyone. The contractor requested to have db owner privs hence was the reason I asked about hiding the security folder. When I tried it with a test user with db owner privs I was still able to modify the sa user account.

    Any ideas?

    K.

  • August 20, 2015 at 6:48 am

    #1821365

    Did you actually try to make the changes to the sa account?

    Although it may let you access the dialogue to make the changes, when you try to apply them it should give you a permissions error.

  • August 20, 2015 at 6:53 am

    #1821366

    Ill test it and get back to you

    K.

  • August 20, 2015 at 7:22 am

    #1821374

    Someone who is just dbo can't make changes to the sa account. Someone who's sysadmin (and dbo as a result of being sysadmin) can.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 20, 2015 at 7:23 am

    #1821375

    hurricaneDBA (8/20/2015)

    Thanks for the reply everyone. The contractor requested to have db owner privs hence was the reason I asked about hiding the security folder. When I tried it with a test user with db owner privs I was still able to modify the sa user account.

    Any ideas?

    K.

    Confirm, are you are talking about the database level \Security folder (users) or the server level \Security folder (logins)? A member of DBO role is allowed to add, drop, or alter permissions for users of that specific database.

    Here is my attempt to recreate what I think you're describing. First I create a new login account with no permissions other than being a member of DBO role on a database called TEST.

    USE [master];

    GO

    CREATE LOGIN [test_dbo]

    WITH PASSWORD=N'A12F72H91', DEFAULT_DATABASE=[Test];

    GO

    USE [Test];

    GO

    CREATE USER [test_dbo] FOR LOGIN [test_dbo];

    GO

    USE [Test]

    GO

    ALTER ROLE [db_owner] ADD MEMBER [test_dbo];

    GO

    When I login as user TEST_DBO, I can add or drop database users under TEST\Security. A member of DBO role can do that at the database level.

    However, TEST_DBO can't go under Server\Security and add, drop, or modify any logins, much less the SA account.

    TEST_DBO can see his own login and the SA login accounts listed under Server\Security (other user logins are hidden), but when TEST_DBO attempts to modify the SA account in some way, it gets an error.

    For example:

    DROP LOGIN [sa];

    Msg 15151, Level 16, State 1, Line 4

    Cannot drop the login 'sa', because it does not exist or you do not have permission.

    When you create user login accounts, confirm that you're not granting them membership in a server level role. By default a new login will be a member of PUBLIC server level role. However, if you grant a login account membership in SYSADMIN role, then they own the server and can do anything you can do. They can even drop you as a SYSADMIN and declare themselves God if they want.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • August 20, 2015 at 7:26 am

    #1821377

    Eric M Russell (8/20/2015)

    They can even drop you as a SYSADMIN and declare themselves God if they want.

    Now there's something for someone's sig..

    DECLARE @myself CHAR(3) = 'GOD';

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 20, 2015 at 7:31 am

    #1821379

    One more thing. If there are any domain groups (like MyCorp\DBA) or local Windows server accounts (like BUILTIN\Administrators) that are members of SYSADMIN database server role, then confirm that this contractor (or anyone else who doesn't need to be sysadmin) are not members of that domain or windows group.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • August 20, 2015 at 7:39 am

    #1821388

    Also confirm that no one's done anything stupid, like granting CONTROL SERVER to public or assigning the sysadmin role to <DOMAIN>\Users (seen both before)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 14 posts - 1 through 13 (of 13 total)

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