locked
Local copies of databases and permissions RRS feed

  • Question

  • Hi,

    I've set up a couple of sqlexpress instances for developers on their own machines, and will be rolling out more over time.

    Developers can restore backups of the production databases onto their local SQL express instances.

    They have been given DBCreator permissions on their local instances but not sysadmin, to prevent them from creating linked servers to the other instances and from altering server level properties.

    However when they restore databases even though they are shown as the database owner in the sys.databases table on the server they are not shown as the database owner in the database (sys.database_principles). As a result they seem to be picking up their permissions from existing user (windows groups) within the restored database. I need them to have db_owner permissions on the restored database without my having to take action for each restore.

    I can fix this issue by running alter authorisation, every time they restore. Obviously if they were made Sysadmin on their own instances this would also get round the problem, but I don't want to do that.

    Does anyone have any suggestions how I can allow them to restore the database and get full permissions on it without making them sysadmin or having to take any other action.


    Sean

    Monday, April 30, 2012 6:16 PM

Answers

  • As Daniel points out, if the developers have the rights to be admin on their machines in Windows, they can install SQL Express (or Dev Edition) and set up linked servers all day long.

    And if they are not admins in Windows, I don't think I would like to work in that place.

    Nevertheless, you could solve your problem in this way. Write a stored procedure for the task to change the owner - best is probably if you bundle the RESTORE with it, so you know that the meddle with the right database. Then sign that procedure with a certificate. Create a login from that certificate, and grant that login the required permissions. Note that this login is just server principal, it cannot actually log in.

    You can find more information about this method on in this article on my web site:
    http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Sean2000 Wednesday, May 2, 2012 6:16 PM
    Monday, April 30, 2012 9:48 PM

All replies

  • several notes:

    Developers can restore backups of the production databases onto their local SQL express instances

    you're aware of the 10GB limitation in SQL Express edition and that it does not support partitioning?

    I've set up a couple of sqlexpress instances for developers on their own machines, and will be rolling out more over time.

    Does anyone have any suggestions how I can allow them to restore the database and get full permissions on it without making them sysadmin or having to take any other action.

    do you developer have local administrator privilege on their machine ?

    if you say YES - then forget about considering restricting SQL Server permissions on their machine.


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, April 30, 2012 7:15 PM
  • As Daniel points out, if the developers have the rights to be admin on their machines in Windows, they can install SQL Express (or Dev Edition) and set up linked servers all day long.

    And if they are not admins in Windows, I don't think I would like to work in that place.

    Nevertheless, you could solve your problem in this way. Write a stored procedure for the task to change the owner - best is probably if you bundle the RESTORE with it, so you know that the meddle with the right database. Then sign that procedure with a certificate. Create a login from that certificate, and grant that login the required permissions. Note that this login is just server principal, it cannot actually log in.

    You can find more information about this method on in this article on my web site:
    http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Sean2000 Wednesday, May 2, 2012 6:16 PM
    Monday, April 30, 2012 9:48 PM
  • Consider GRANT ALTER ANY DATABASE TO <login>; on the developer computers.

    And Daniel is correct. If the developers are members of the local administrators group, they can escalate to sysadmin on their instance of SQL Server.


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

    Monday, April 30, 2012 10:06 PM
  • Thanks for all the replies, food for thought.

    I will play around with the suggestions but I take the point about the developers have local admin rights which they mostly do.


    Sean

    Tuesday, May 1, 2012 2:30 PM
  • Thanks, I created a restore procedure and used a certificate.

    I already had your grant permissions page in my bookmarks as I've referred to it several times.

    A good article, and good advice.

    greatly appreciated.


    Sean

    Wednesday, May 2, 2012 6:18 PM
  • I don't believe that it will solve your concern about restoring and access the database by your developer.

    why do you want to restrict your developer what they can do with their SQL Server ?

    What you really should have to ask your self why do you give access to a backup of your production database to all your developers ?

    Personally I think that is a much more critical topic than restricting what they can do with their local SQL Server - especially if the database contains privacy data or intellectual property of your company.


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, May 2, 2012 7:57 PM
  • Thanks,

    I've taken this on board as well as some of the earlier comments and I've raised these concerns.

    Using the signed certificate and a restore procedure means that the user can be made the owner of the restored databases.

    Access to the backup files is controlled, also we automatically create cut down versions of various databases for restoring to SQLexpress containing some data and all the objects.

    The main concern is that Developers will start linking to other instances to run queries and extract data, which is why their permissions have been limited.


    Sean

    Thursday, May 3, 2012 11:47 AM
  • I don't know your scenario, but it sounds like developers can just create their own instances of SQL Server, and link to the production DB's and "run queries and extract data." Or use Microsoft Access or Excel, or connect using SQLCMD. The list of ways to connect is endless. The access control that protects the data must be on the server that holds the data.

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

    Thursday, May 3, 2012 4:14 PM
  • The main concern is that Developers will start linking to other instances to run queries and extract data, which is why their permissions have been limited.

    just give them no access to the production server database with their regular user account because

    If they need access to the database but only by the use of controlled application, use a logon trigger on the production servers which is executed when the user connect to the database and than you can check for allowed application, restricted ip address etc.

    Additionaly you can audit all the access of the data.


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, May 3, 2012 7:13 PM
  • Using the signed certificate and a restore procedure means that the user can be made the owner of the restored databases.

    It's good to hear that you have that part working.

    But as long as the developers are administrators on their machines,
    they can install an Express instance where they have all the powers.
    (Or just start the instance you have given them in single-user mode.)

    So it is not apparent what you are gaining.

    As Daniel and Rick points out, you need to limit their access rights where the data is.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, May 3, 2012 9:42 PM