Summary:
This tutorial is about creating a new user, new database, and assigning the new user a role in Microsoft SQL Server 2008 R2.
Description:
We will do a step-by-step walkthrough of how to add a create a new database, create a new user, and add the user to the "db_owner" role for the new database in Microsoft SQL Server 2008 R2.
This is one of the first steps in starting to use Microsoft SQL Server 2008 R2.
Before we start:
This tutorial assumes you have Microsoft SQL Server 2008 R2 Express Edition installed. I assume you enabled Windows Authentication and have a default instance of SQL Server.
Steps:
1. Open MS SQL Server 2008 R2 - Go to 'Start', 'Microsoft SQL Server 2008 R2', and select 'SQL Server Management Studio'.
2. Connect as Admin - If your installation went well, you should be able to set "Authentication" to "Windows Authentication", server name to "(local)", and click the "Connect" button. As one alternative, you can use your computer name as the server name. Another alternative is to click the drop-down box for server name, select "browse", and find your server name under "Database Engine".
3. Create a TestDatabase - On the left-hand side is a section called the "Object Explorer". In the Object Exporer, right-click on "Databases", and select "New Database...". A new window appears named, "New Database".
In this window, find the textbox labeled "Database name:" and write "TestDatabase". After this, click "OK" to create the new database.
4. Create the New User - Go to the Object Explorer and open the Security folder. Inside, you will find a folder named "Logins". Right-click on the folder named "Logins" and select "New Login".
For the login name, type "mytestuser". Below this, select the radio button labeled "SQL Server authentication". In the Password boxes, type "password" or something easy to remember. NOTE: It's not good practice to use easy passwords like this, but this is just for learning. Next, make sure the following three checkboxes are not selected: "Enforce password policy", "Enforce password expiration", and "User must change password at next login". Lastly, on the left-hand side under "Select a Page", select "User Mapping". Don't select the OK button yet... move to the next step.
5. The New User's Role - Before we create the new user, we want to make the new user a member of the "db_owner" role for the table we created in step 2. After selecting "User Mapping" in the previous step, look for your database "TestDatabase" from step 2 in the top half of this window. Next to the name "TestDatabase" is a checkbox, select this check box. With this checkbox selected and this row highlighted, the bottom half of the current window will show all of the "Database role membership for: TestDatabase". In this area, click the checkbox next to "db_owner". Now, select the OK button.
6. Done!! Login with the new user - The database, user, and mapping are all done. We now need to test everything we've done by logging in using the new user we've created and access the new database. So.. Close and reopen Microsoft SQL Server Management Studio. (Another way is to select "Disconnect Object Explorer" and then select "Connect Object Explorer" under "File" menu.) Either way, the login screen should appear. In the drop-down box labeled "Authentication", select "SQL Server Authentication" from the drop-down box. Now, the "Login" and "Password" boxes will become enabled. For login, type the name of the user we created, "mytestuser". For password, type the password we used, "password". Select the "Connect" button. That's it! Lastly, if you want to make sure we assigned "mytestuser" the "db_owner" role, open the "TestDatabase". If you can open this database, without error messages, everything should be good. If you want to see what happens when you don't have permission, try to open any other database.
Comments/Questions?
I'm always wanting to improve the quality of the content here. If you have questions, want to see a better explanation, a better picture, let me know. Thanks - Sean
This tutorial is about creating a new user, new database, and assigning the new user a role in Microsoft SQL Server 2008 R2.
Description:
We will do a step-by-step walkthrough of how to add a create a new database, create a new user, and add the user to the "db_owner" role for the new database in Microsoft SQL Server 2008 R2.
This is one of the first steps in starting to use Microsoft SQL Server 2008 R2.
Before we start:
This tutorial assumes you have Microsoft SQL Server 2008 R2 Express Edition installed. I assume you enabled Windows Authentication and have a default instance of SQL Server.
Steps:
- Open MS SQL Server 2008 R2
- Connect as Admin
- Create a TestDatabase
- Creating the New User
- The New User's Role
- Done!! Login with the new user
1. Open MS SQL Server 2008 R2 - Go to 'Start', 'Microsoft SQL Server 2008 R2', and select 'SQL Server Management Studio'.
2. Connect as Admin - If your installation went well, you should be able to set "Authentication" to "Windows Authentication", server name to "(local)", and click the "Connect" button. As one alternative, you can use your computer name as the server name. Another alternative is to click the drop-down box for server name, select "browse", and find your server name under "Database Engine".
3. Create a TestDatabase - On the left-hand side is a section called the "Object Explorer". In the Object Exporer, right-click on "Databases", and select "New Database...". A new window appears named, "New Database".
In this window, find the textbox labeled "Database name:" and write "TestDatabase". After this, click "OK" to create the new database.
4. Create the New User - Go to the Object Explorer and open the Security folder. Inside, you will find a folder named "Logins". Right-click on the folder named "Logins" and select "New Login".
For the login name, type "mytestuser". Below this, select the radio button labeled "SQL Server authentication". In the Password boxes, type "password" or something easy to remember. NOTE: It's not good practice to use easy passwords like this, but this is just for learning. Next, make sure the following three checkboxes are not selected: "Enforce password policy", "Enforce password expiration", and "User must change password at next login". Lastly, on the left-hand side under "Select a Page", select "User Mapping". Don't select the OK button yet... move to the next step.
5. The New User's Role - Before we create the new user, we want to make the new user a member of the "db_owner" role for the table we created in step 2. After selecting "User Mapping" in the previous step, look for your database "TestDatabase" from step 2 in the top half of this window. Next to the name "TestDatabase" is a checkbox, select this check box. With this checkbox selected and this row highlighted, the bottom half of the current window will show all of the "Database role membership for: TestDatabase". In this area, click the checkbox next to "db_owner". Now, select the OK button.
6. Done!! Login with the new user - The database, user, and mapping are all done. We now need to test everything we've done by logging in using the new user we've created and access the new database. So.. Close and reopen Microsoft SQL Server Management Studio. (Another way is to select "Disconnect Object Explorer" and then select "Connect Object Explorer" under "File" menu.) Either way, the login screen should appear. In the drop-down box labeled "Authentication", select "SQL Server Authentication" from the drop-down box. Now, the "Login" and "Password" boxes will become enabled. For login, type the name of the user we created, "mytestuser". For password, type the password we used, "password". Select the "Connect" button. That's it! Lastly, if you want to make sure we assigned "mytestuser" the "db_owner" role, open the "TestDatabase". If you can open this database, without error messages, everything should be good. If you want to see what happens when you don't have permission, try to open any other database.
Comments/Questions?
I'm always wanting to improve the quality of the content here. If you have questions, want to see a better explanation, a better picture, let me know. Thanks - Sean
Great no-nonsense tutorial on how to set up a new user. I get it, thanks!
ReplyDeleteThis is awesome!! really helpful for me. Thanks for sharing with us. Following links also helped me to complete my task.
ReplyDeletehttp://msdn.microsoft.com/en-us/library/aa337562(v=sql.105).aspx
http://www.mindstick.com/Blog/401/Create%20User%20Login%20in%20SQL%20Server%202008%20R2
hi i created a user like this but that user having access to all the dbs in my server how can we restrict that guy has to access only that db?
ReplyDeletehi Automation Testing Tools try to this blog post it's help full to you
ReplyDeletehttp://mcakrunal.blogspot.in/2015/08/create-new-database-user-in-sql-server.html