1 Working with Management Studio Express

1 | Working With Management Studio Express

SCC Study Group
SQL Server Management Studio Express

Check out the three videos I made under Series 2 for SQL

How to change Server authentication mode:

1.Go to SSMSE and rt. Click on the server.
2.Choose properties. In the box shown, choose security and then set authentication mode. Windows mode is much more secure! But SQL users MUST have a Windows domain or local account.

Backups:

1. Update master db and your own databases regularly!! You cannot schedule tasks in SSMSE.
2.Rt. Click System | Master and choose tasks | backup.

Restore a DB

1.Copy db to Backup folder.
2.Under databases, choose Tasks | Restore DB and select the db you want.

How to Save your DB to a script file

1.Rt click on your db and choose Tasks | Generate Scripts
2.Choose all the stuff you want scripted. (I choose everything in the wizard)
3.Tell it to store it in a file on the desktop.
4.When finished, double-click it (with SSMSE open) and you will see the code required to re-create the db. You can send this to someone and they can create the db on their machine!

This shows the .sql file for our customers-05.mdf

How to create a new login:

1.Create a new Windows user account.
2.Create a new SQL login. You must be a sysadmin to make logins
3.Choose security under the server (not the database you are working with).
4. Rt. Click and choose New Login. Click search and type the name then choose check names then OK.

/ 5. At this point, you can assign the name of the database you want them to have access to. I would not use Master as the default because that manages your entire SQL server. These login names are maintained within the master database. So, it is essential to back up the master database after adding new logins to SQL Server.

Add New User to Existing DB

We need to add our new user login to a db..say customers-05. So rt.
1.Click on customers-05 and choose security. Make sure you use the folder under the customers-05 db. Under Users, notice the karenaccount we just made.

2.Now double-click Karen to open up her properties page. Put a check mark for db_datareader. This will let her read the DB but that is all! Then click OK. The Roles are like groups with pre-defined permissions.

3.Now, let’s assign user permissions to the database objects. Choose Securables. Notice the list is empty.

/ 4. Click on Search then choose Specific objects from the message box & then OK.

5. Then choose Object types.

6. Then select Tables. OK.

7. Now select the tables we want her to read.

8. Now, highlight each table and check Select in the permissions below. Notice that we could restrict her access to only certain columns by clicking the Column Permissions button. But this seems a bit controlling since she can only read the db anyway. But if you needed to do this, here is the place. Highlight each table and grant her Select permissions.

Testing our permissions

1. Switch computer users and login as karen (no pw).

2.Now log into SSMSE using karen’s account.

3.Click on the customers table and choose Edit top 200 records. Try to add a new record.

4.If we did things right, karen will be denied permissions when she tries to save the new record!