Creating SQL Server Limited User Account

Sometimes you need to create a limited user account in SQL Server 2005. This is a trivial task but I find many new DB developers struggling to do so. However, if you understand the underlying concept, it indeed is trivial.

This is how I find new DB developers creating a limited user account:

1. Open up SQL Server Management Studio
2. Expand the desired DB > Security
3. Right click on Users and select New User…
4. In the Database User – New dialog, enter in a User name following by a Login name. Usually both are the same
5. Bang. This is where you get the error “Error 15007 when trying to add a new user”

So what went wrong in the above steps? The answer is that you cannot create a User Role without first creating a Login. A Login connects to an SQL Server instance while a User Role defines the database access level. In other words:

Login – SQL Server Level
User – Database Level

Now to create a limited user account, you following steps have to be followed:

1. Open up SQL Server Management Studio
2. Expand Security. You will see a Logins node. Right click on Logins and click on New Login…
3. In the Login – New dialog, enter in a Login name
4. Next click on SQL Server Authentication radio button. Enter and confirm Password.
5. From Default database, select the desired database. Now you are done creating a Login. The next step is to create his specific role
6. Under the SQL Server instance node, Expand Databases > [Database] > Security. You will see a Users node.
7. Right click on Users and select New User…
8. In the Database User – New dialog, under General page, enter in User name
9. In front of Login name, click on the browse (…) button. You will see a Select Login dialog
10. Click on Browse button and check the Login you created above and click OK. Close the Select Login dialog by click on OK
11. Next under Database User – New dialog, click on Securables page. Click on Add button. This will open up Add Objects dialog.
12. Select Specific objects and click OK. This will open up Select Objects dialog.
13. Click on Object Types button. This will open up Select Object Types dialog.
14. Check the desired object type (Tables, Views, Stored Procedures etc) which Login will have access to. Click OK
15. Under Select Objects dialog, click on Browse button. This will open up Browse for Objects dialog. Select the desired objects which Login will have access to. Click OK.
16. Click on OK to close Select Objects dialog.
17. Under Database User - New, you can select each object in Securables list and specify permission level on each object in the Explicit permissions for list
18. Click on OK to close the Database User - New dialog. You have now set permissions on the specific user.