locked
user access question after db migration RRS feed

  • Question

  • I just restored a SQL db to a new SQL server. The application uses the SA account to access the database in the old server. On the new server, I would like to configure the application to use a non SA account to access the database. What I did so far is created a new sql account on the new server and gave db_owner role to the restored db. I have also configured the application to use the new account to access the database. I have not tested this new setup and would like to know if this is sufficient enough for the db access. The database uses the default schema which is owned by the dbo user account. I am wondering if I have to change to ownership of the dbo schema to the new sql user account I created.

    Thanks

    Wednesday, December 16, 2015 10:22 PM

Answers

  • Provided that the application does not require to use any functionality that is not self-contained in the database (i.e. no instance level functionality), then db_owner should suffice.

    However, longer term you should also look to reduce the database permissions to only the appropriate level as well (i.e. GRANTing on EXECUTE on the required stored procedures and SELECT, DELETE etc on tables that are addressed directly).

    The ownership of the schema and the permissions on the objects in the schema have been separated since schemas were introduced. Although the ownership is still important, the specific permissions are more relevant.  Saying that, if your USER is db_owner, they are in fact mapped to "dbo" which means that the USER will have full access to all objects in the dbo schema.


    Martin Cairney SQL Server MVP

    Wednesday, December 16, 2015 11:30 PM
  • Hi,

    Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.

    For the security reasons it is always recommended to restrict the access/permission only to required objects.

    Create a custom database role around those objects & permissions and attach that role to the application account (Login).


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Thursday, December 17, 2015 4:48 AM
  • Thanks for all the input. Looks like the db_owner role is good for my setup for now.

    Thanks again.

    Thursday, December 17, 2015 6:48 PM

All replies

  • Provided that the application does not require to use any functionality that is not self-contained in the database (i.e. no instance level functionality), then db_owner should suffice.

    However, longer term you should also look to reduce the database permissions to only the appropriate level as well (i.e. GRANTing on EXECUTE on the required stored procedures and SELECT, DELETE etc on tables that are addressed directly).

    The ownership of the schema and the permissions on the objects in the schema have been separated since schemas were introduced. Although the ownership is still important, the specific permissions are more relevant.  Saying that, if your USER is db_owner, they are in fact mapped to "dbo" which means that the USER will have full access to all objects in the dbo schema.


    Martin Cairney SQL Server MVP

    Wednesday, December 16, 2015 11:30 PM
  • Hi,

    Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.

    For the security reasons it is always recommended to restrict the access/permission only to required objects.

    Create a custom database role around those objects & permissions and attach that role to the application account (Login).


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Thursday, December 17, 2015 4:48 AM
  • Hi There,

    SA account is the admin on the instance level. the user you created as db owner is admin on that database only. not on the instance level. SO if you application is only need admin access on that database then you should be fine.

    All the best

    Kumar

    Thursday, December 17, 2015 5:15 AM
  • Thanks for all the input. Looks like the db_owner role is good for my setup for now.

    Thanks again.

    Thursday, December 17, 2015 6:48 PM