Create user and add to role / schema

Posted on 2009-04-22
Last Modified: 2012-05-06
I have a SQL Server 2005 database. 100 tables or so which i am right now accessing using the sa login, as this is just dev at the moment i have not worried too much about that.

I have 5 different client applications in the field, the only way people access the server data. I want to make 5 different logins, one for each application. I guess a schema would be the way to go, or a role in a schema?

As you can tell i am new to the security side of logins and have no idea where to start. Do i do it with the GUI, or T-SQL (either way i am comfortable). How do i set the permissions on each table.

As much of a step by step tutorial would be great. I am not using Windows groups or accounts, strictly sql logins.

Question by:Halon
    LVL 12

    Accepted Solution

    You will have to create different account for different applications to have different privileges on different tables (irrespective of the schemas).
    One way of defining this is by creating different roles with different privileges on the tables (and/or schemas).
    The other way (second) is by creating different accounts for the applications and assigning different database/table permissions (or privileges) to these accounts.
    In SQL 2005, you can create a user account by connecting to your SQL instance using SQl server management Studio (SSMS), expand the server, righ-clicking the security folder, and choosing "New" > "Login"
    Specify the login name, (and passwd in case it's a SQL account). Remember to uncheck "Enforce pawwrod policy" in the general tab, since these are application accounts. Also change default database to the application account database.
    NB: Enforcing passwd policy will pickup your NT policy affecting the server from AD or local server and applying it to your SQl accounts as well.
    Do not grant any server roles to application accounts (user server roles).
    Specify the user mappings (this way, you grant the account a login to specific databases you want this account to access). Public role is picked by default.
    db_datawriter is for writing to any table just like db_datareader role does the reading.
    After creating the account, you can further restrict access to the tables and/or schemas too by going to account properties.
    Using SSMS for a starter is simpler but if you want the queries to d othe same, let me know.

    Author Closing Comment

    Thanks Chris, got it working!!

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now