Create user and add to role / schema

Posted on 2009-04-22
Medium Priority
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:Winston Smith
LVL 12

Accepted Solution

Chris M earned 2000 total points
ID: 24206298
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

by:Winston Smith
ID: 31573312
Thanks Chris, got it working!!

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

864 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