none
set application to sql server

    Question

  • Hi,

    fresh SQL 2012 installed. Planned to serve couple of applications.

    Server was properly partitioned for the beginning of the project. Some questions may come later... For now I have few.

    What info should be provided to Application supplier that will need to use SQL?

    1. SQL server name. In what format?

    2. User rights. The standard user account will be created for the App supplier.

    What are the correct Rights that should be assigned to that user on SQL server if any...

    The standard account will be added to local admin group on Application server.

    How correctly manage his access to DB? Please provide step by step instructions or precise doc. I am not a DB admin.

    And did SQL installations on APP servers directly.

    I guess I have to create a DB on SQL server (for eliminating any access of supplier to SQL server) and then provide the right path (server name\DB name).

    Please help.

    Thanks.


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis


    • Edited by pob579 Thursday, February 2, 2017 12:44 PM
    Thursday, February 2, 2017 12:43 PM

Answers

  • Yep you are correct, and using GUI is easy but TSQL is more reliable

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by pob579 Sunday, February 5, 2017 12:14 PM
    Sunday, February 5, 2017 6:27 AM
    Moderator

All replies

  • 1) You will need the instance name and the server name. To get this connect to your SQL Server in management studio and issue the query

    select @@ServerName

    The application or application vendor will likely create their own database. You should not have to provide them with this info.

    2) That is a hard one. As the application may be creating database users, and databases it should be in at least the dbcreator and securityadmin server roles. It should be in the db_owner role in the database itself.

    The vendor should know this information already. I would ask them specifically what they require and whether the install package will create all necessary objects and what role should it be in.

    • Proposed as answer by Uri DimantMVP Thursday, February 2, 2017 1:15 PM
    Thursday, February 2, 2017 12:59 PM
  • Hilary thanks for rapid answer...

    want to clarify:

    > As the application may be creating database users, and databases it should be in at least the dbcreator and securityadmin server roles. It should be in the db_owner role in the database itself.

    If I understand correctly this will allow Apps vendors (in case will be more than one) be able to "touch" instances that are not related to his app if all of them will have securityadmin role.

    I think there should be a way that I can create an instance/db and provide full access rights to related App owner?


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Thursday, February 2, 2017 1:36 PM
  • Miles,

    I am afraid most of your questions are too wide and I as a person responding on forum cannot tell you what rights account should have. I have seen application using system admin account to access database and that was fine with management while on other hand most people have account with data reader and data writer rights and if required execute permission on specific stored procedures. The list can go on so I guess you have to sit down with application developer ask what all things application needs to do and provide rights according to that. Definitely syadmin right should not be given.

    People also ask supplier to sight some non disclosure agreement when they provide rights to access their database.

    You must ask application provider what they need and get the checklist and then may be tell us this is what he has asked for so that we can give some pointers as to give him or not 


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, February 2, 2017 1:37 PM
    Moderator
  • Thanks Shanky...

    Sure I will talk to the supplier. There is a chance that they never worked with shared SQL (as many of our suppliers) and used a SQL on App server. Sure I will talk to them. It could be the case that even SQL Express will suit this particular App.

    This whole question of setting up a Dedicated (shared) SQL server for future projects just felt on my head.

    I did deploy SQLs with always following best practices and forum advices. Some of implementations were for APPV and RDS. But it was one SQL server per APP.

    The current situation where I was asked to prepare a SQL for multiple apps is a bit challenging. But I guess with general right approach and forum's help I will be able to start correctly. Sure I have to learn more.

    The plus is - there is no rush for SQL deployment for multiple Apps. So I have to deal properly with the first one.

    Sorry for repeating the question... just want to know if I am thinking correctly...

    Can I create a NewDB under Databases and then assign the roles to each DB?

    In my perspective it should eliminate cross rights to DBs?

    Thanks again for advices. I will talk to vendor to have all the details of SQL needs.

    Michael.


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Thursday, February 2, 2017 2:09 PM

  • Can I create a NewDB under Databases and then assign the roles to each DB?

    In my perspective it should eliminate cross rights to DBs?

    Thanks again for advices. I will talk to vendor to have all the details of SQL needs.

    You can create databases no issues, but roles are assigned to logins and users and that role will permit users to do specific task.

    What do you mean by cross rights ?


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, February 2, 2017 2:24 PM
    Moderator
  • by cross rights I wanted to say:

    that User1 can access DB2 and User2 can access DB1 in case that both Users 1 and 2 will have securityadmin role


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Thursday, February 2, 2017 2:28 PM
  • > but roles are assigned to logins and users and that role will permit users to do specific task.

    Thanks for pointing out... now it is clear that 2 users having securityadmin roles will be able to access all DBs.

    It is not on DB based... Now I understand Hilary's points. And clearly understand the importance of planning with having answers to all questions from the app vendor. Finally, roles could be set temporary for the installation time and then tuned up for real needs.

    Just checked logins and got what you explained... First user with Local Admin rights is there... with the BIG role.


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Thursday, February 2, 2017 2:47 PM
  • I spoke with the vendor over the phone... here are answers to some of my questions:

    1.

    Q: what SQL version is supported by the app? (Initially, they asked 2008)

    A: 2012 2014 and 2016

    2.

    Q: what type of files would be transferred to DB?

    A: Any (no restrictions) - photos and video probably be a part of the list (have to check with our User).

    3.

    Q: What user rights are necessary for working with DB?

    A: DBOwner on DB, Public in Server Roles.

    **********************

    From the above answers I will check the possibility to install SQL 2016, instead of 2012 ready.

    Since the data base could grow up in future I will talk about archiving plan and maintenance. The server will be daily fully backed up.

    There will be 5 users using the Application.

    So, my question about User Rights could be answered more precisely...

    I am planning to create a Standard AD user account for the vendor.

    I will include it in local Admin group on Application server.

    Please provide detailed step for properly adding the above Standard user in Management Studio to make it able to create a DB.

    Probably, some elevated rights will be needed during App setup. Then I can remove it?

    Should I add to "Logins" 5 app users (beside the vendor account) or these 5 users could be added through the app?

    Any other advices will be appreciated. 


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Saturday, February 4, 2017 3:29 AM
  • OK DB_owner role is fine but let me tell you that aperson with DBowner role can do any task in that database. He can delete,insert,update or even drop the database which I am sure the App owner will not do. With DBowner the application can make any change in the database.

    ONLY application should have dbowner role the users who are accessing why they need dbowner, I suggest db_datareader, db_datawriter would be fine. If requirement goes on ddladmin can also be given.

    --you need to create login first
    use master
    go
    CREATE LOGIN [<domainName>\<login_name>] FROM WINDOWS;  
    GO  
    --then create user in the database and then map it to login
    use database_name
    go
    CREATE USER [UserName] FOR LOGIN [<domainName>\<login_name>]
    go
    EXEC sp_addrolemember N'db_owner', N'user_name'

    The above will create login and then create user and map it to login and then provide db_owner


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Saturday, February 4, 2017 5:17 AM
    Moderator
  • since it is the fresh SQL install there are no DB beside Master.

    Sure I don't want to assign DB_owner to the vendor on Master DB.

    Please correct my newbie thinking that:

    1. I should create a DB

    2. Create a Login for the AD user "Vendor" (that is a standard AD user), than add the same user "Vendor" to Users

    3. Map user to Login created in step 1.

    4. Assign the DB_owner Role to mapped user.

    I think that this will give full rights to the vendor to do whatever he needs with his DB (he will be responsible for it) eliminating access to all other aspects of the SQL server.

    Please correct me. Also, I guess I will use the GUI until I will learn more and will touch SSMS more often :).

    Sure I have to learn and practice more... and I will do it. The whole topic is very interesting for me. I just was not involved in SQL management before.

    Thanks for help.


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Sunday, February 5, 2017 2:37 AM
  • Yep you are correct, and using GUI is easy but TSQL is more reliable

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by pob579 Sunday, February 5, 2017 12:14 PM
    Sunday, February 5, 2017 6:27 AM
    Moderator
  • Shanky.

    If you garantie that DB owner will be a god :) of his DB, I think I got the idea... and the steps I put above will serve me as a template for adding other DBs and DB-Owner in which case I can achieve my primary goal to secure DBs by vendor.

    Thanks!


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Sunday, February 5, 2017 12:21 PM
  • Shanky.

    If you garantie that DB owner will be a god :) of his DB, I think I got the idea... and the steps I put above will serve me as a template for adding other DBs and DB-Owner in which case I can achieve my primary goal to secure DBs by vendor.

    Thanks!


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis


    Buddy you really want to take guarantee from person who you are taking virtually and who does not have any/very very less  knowledge about your system.  I am not guaranteeing anything I am just trying to *think* what could be best for your scenario. If vendor says he requires db_owner you dont have much option. But apart from that steps ate correct which I pointed.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Sunday, February 5, 2017 2:02 PM
    Moderator
  • do you want me to pay for the garantie?

    Sure I am kidding :)

    ... Just was glad to see your confirmation that my 4 steps are theoretically right.

    Thanks!


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Sunday, February 5, 2017 11:27 PM
  • do you want me to pay for the garantie?

    Sure I am kidding :)

    ... Just was glad to see your confirmation that my 4 steps are theoretically right.

    Thanks!


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Meanwhile if you face any issue raise a new thread giving reference of this thread, lot of people will respond to that. Lets us just close this thread it has already gone big.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, February 6, 2017 5:21 AM
    Moderator