locked
permissions on sa login RRS feed

  • Question

  • our applications are using sa to connect to sql serer

    we want to get away from that

    we are attempting to rename sa to be satemp and assign with limited permisisons

    to accomplish this I am attempting to understand all dependencies on sql server objects on the sa account

    how to determine what objects are owned by sa

    is there a script to determine the effective permissions of a login , especially sa

    Wednesday, August 12, 2020 5:30 AM

All replies

  • we are attempting to rename sa to be satemp and assign with limited permisisons

    Even if you rename "sa" admin account, it is still the SysAdmin account and you can not restrict the permissions of it in any way.

    Create a new SQL Login and grant only the required permission needed by the application.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Wednesday, August 12, 2020 5:56 AM
  • we want to rename and assign only limited permissions--I did not specify that

    how to identify all dependencies and permissions on the current sa login before we do that

    Wednesday, August 12, 2020 6:24 AM
  • we want to rename and assign only limited permissions--I did not specify that

    You can rename sa, but you can not change the permissions for sa. But you can create a new application login with limited permissions.

    how to identify all dependencies and permissions on the current sa login before we do that

    Testing!

    You decide which permissions you want to assign to the new application login. Ideally, this should only be EXECUTE permission on stored procedures, but if the application submits SQL statements directly, you will have to add the application user to db_datareader and db_datawriter. But you should absolutely not go beyond that.

    Then you set up a test environment and monitor all places where you get permission errors and for these you will need to rewrite things. A very good technique is to put such actions in stored procedures which you sign with a certificate, and then you can tie permissions to the certificate. I describe this technique in detail in this article on my web site: http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, August 12, 2020 6:46 AM
  • Can I rename sa to satemp remove the sysadmin permissions but make it a dbo on all databases

    is there a script to check the effective permissions of a login, in this case sa

    Wednesday, August 12, 2020 6:57 AM
  • Can I rename sa to satemp remove the sysadmin permissions but make it a dbo on all databases

    NO! sa remains sysadmin no matter what you rename it to.

    And the application should run neither as sa nor as dbo!


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, August 12, 2020 10:48 AM

  • is there a script to check the effective permissions of a login, in this case sa

    The sa login (and all sysadmin role members) is special. Simply stated, sysadmin permissions are not checked at all so effective permissions is everything. There is no need to check effective permissions of sysadmin role members.

    To restate, you must create a new login for the application and grant the needed permissions. You can separately rename the sa login if you like but applications should not use that login since it will always be a sysadmin role member and have all permissions.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Wednesday, August 12, 2020 12:08 PM
  • Hi

    Yes you can not restrict .The bellow query give you the permission in a dababse .If you need the table label

    Then incited of database put table name

    EXECUTE AS LOGIN = 'sa';  
    SELECT permission_name FROM fn_my_permissions('databasename', 'OBJECT')   
        ORDER BY subentity_name, permission_name ;    
    REVERT;  
    GO  

    Thanks and regards

    Wednesday, August 12, 2020 12:09 PM
  • Thank you, The 'SA' login permissions cannot be modified , however it can be renamed and disabled right.

    In that case, all dependencies on 'SA' must b resolved because it has now been disabled.

    Thursday, August 13, 2020 3:00 PM

  • In that case, all dependencies on 'SA' must b resolved because it has now been disabled.

    That's good news. Now you need test to find out where things break and then decide how to deal with those situations. And remember: nothing stronger than db_datareader/writer, and preferably on EXECUTE permission!

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, August 13, 2020 3:14 PM
  • Thank you, The 'SA' login permissions cannot be modified , however it can be renamed and disabled right.

    In that case, all dependencies on 'SA' must b resolved because it has now been disabled.

    Hi neophytenik,

    The 'SA' is an administrative account that has full access and complete control over the SQL Server instance and all its databases. It can be renamed and disabled, but it cannot be deleted, and its permissions cannot be restricted while it is enabled.

    Best regards,
    Cris


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Friday, August 14, 2020 8:42 AM