Quick Tutorial - Users and Permissions in SQL Server

Summary

This video explains how to manage SQL Server permissions by creating users, assigning, and restricting their access. It begins with setting up a user in SQL Server Management Studio, assigning a login, and providing database access. The tutorial then dives into managing table-level permissions, showing how to restrict users from viewing or altering specific tables. Additionally, it covers column-level permissions, demonstrating how to allow access to certain fields while denying access to others. Finally, the process is tested by logging in as the user to verify the permission settings.

Takeaways

• 🔑 Permissions in SQL Server are used to control access to specific tables or data, allowing you to restrict or assign access based on the user.
• 👤 To create a user in SQL Server Management Studio, navigate to the 'Security' area, select 'Logins,' and create a new login with a username and password.
• 🔐 SQL Server authentication is often chosen when creating a new user, allowing for more flexible password management and authentication options.
• 📂 After creating a user, you'll need to map them to a database and assign specific roles such as 'DB Owner' to grant them access.
• 🚫 You can restrict access to specific tables for a user by navigating to the table's properties and using the permissions tab to deny access to that table.
• 👁 Users can have access to some tables in a database while being restricted from others by adjusting the table-specific permissions.
• 📄 It's also possible to restrict access to specific fields or columns in a table, allowing users to view only certain columns while hiding sensitive data.
• 🔍 When restricting column-level access, you can select permissions for each column individually by using the 'Column Permissions' option in SQL Server.
• 👨‍💻 Once the permissions are set, logging in as the user will allow you to test the access and verify that the restrictions or grants are working as intended.
• 🛠 The process of managing users and permissions in SQL Server involves creating users, assigning database access, and customizing permissions at the table and column level.

Q & A

What is the purpose of assigning permissions in SQL Server?

Permissions in SQL Server control user access to specific tables, data, and other resources, allowing administrators to restrict or grant access to ensure data security and integrity.

Where do you create a new user in SQL Server Management Studio?

To create a new user in SQL Server Management Studio, you go to the 'Security' section, expand it, right-click on 'Logins,' and select 'New Login' to create a user.

What authentication method is recommended when creating a user in SQL Server?

It is recommended to use SQL Server Authentication when creating a user, where you assign a username and password for the login.

What is 'User Mapping' used for when creating a new user?

User Mapping allows you to grant a user access to specific databases within SQL Server, ensuring the user can only interact with the databases assigned to them.

How can you restrict a user's access to specific tables in a database?

To restrict access to specific tables, you go to the 'Properties' of a table, navigate to the 'Permissions' tab, search for the user, and deny the necessary permissions for that table.

What are 'Column Permissions' and how are they used?

'Column Permissions' allow you to restrict user access to specific fields (columns) within a table. You can grant or deny permissions for individual columns based on user needs.

How do you test whether the user permissions were correctly applied?

To test user permissions, you log in as the created user and attempt to access the restricted resources. If permissions were correctly set, the user will receive 'Permission Denied' errors for restricted tables or columns.

What does 'DB Owner' access provide to a user?

'DB Owner' access gives the user full control over the database, allowing them to manage everything from modifying data to changing the database structure.

What happens if a user tries to query a column they don't have permission to access?

If a user tries to query a column they don't have permission to access, they will receive an 'Access Denied' error, preventing them from viewing the restricted data.

Why is it important to enforce strong passwords in a production environment?

In a production environment, enforcing strong passwords is critical for maintaining database security and preventing unauthorized access to sensitive data.

Outlines

00:00

🛠️ Understanding SQL Server Permissions

In this paragraph, the speaker introduces the concept of permissions in SQL Server. Permissions are important for controlling access to data, such as preventing users from viewing or altering specific tables. The speaker explains that the goal is to set up user accounts and restrict permissions, with a demonstration on how to create a user account in SQL Server Management Studio. The process involves navigating to the 'Security' area, selecting 'Logins,' and setting up the user with SQL Server authentication, assigning a simple password, and configuring basic access to databases.

05:02

🔒 Assigning and Restricting Table Permissions

The speaker demonstrates how to assign specific permissions to a user, using an example of limiting a user’s access to particular tables in a database. They walk through how to deny access to certain tables after a user is created, explaining the steps involved: right-clicking on the table, selecting 'Properties,' navigating to the 'Permissions' tab, searching for the user, and setting specific permissions for the tables. In this case, the user 'Bobby' is used as an example, and permissions are restricted so that Bobby cannot access certain tables.

10:03

📊 Restricting Column-Level Permissions

This paragraph focuses on restricting permissions at the column level within a table. The speaker describes a scenario where a user, like Bobby, is allowed to view some fields but is restricted from seeing others, such as sensitive information like a Social Security Number. The process involves assigning 'Select' permissions for specific columns, where some fields are granted access and others are denied. The detailed steps include using 'Column Permissions' and setting grants and denies for particular fields, ensuring Bobby can only see the authorized data.

Keywords

💡Permissions

Permissions in SQL Server control what actions a user can perform on a database. They can be used to restrict or grant access to certain tables, databases, or even specific columns. In the video, permissions are shown as a way to manage user capabilities, such as allowing or denying access to specific tables or fields within a database.

💡User

A user in SQL Server refers to a specific login or account that is granted access to perform operations on a database. In the video, the user ‘Bobby’ is created and given certain access permissions to a database. Users are critical in managing database security and assigning roles.

💡SQL Server Authentication

SQL Server Authentication is a method of securing user access to SQL Server by requiring a username and password. In the video, this method is chosen when creating a new login, using a simple password. It contrasts with Windows Authentication and is typically used in environments where SQL Server does not integrate with a Windows domain.

💡Database Owner (DB Owner)

DB Owner is a high-level role in SQL Server that grants the user full control over a specific database. In the video, the user is granted DB Owner permissions to certain databases, allowing them to manage and modify the databases entirely.

💡Table

A table in SQL Server is a collection of data organized in rows and columns. Permissions can be set to control who can access certain tables. In the video, specific permissions are assigned to prevent the user from viewing or altering certain tables, demonstrating how SQL Server restricts access on a table-by-table basis.

💡User Mapping

User Mapping in SQL Server assigns a user to specific databases and configures their role or level of access. In the video, user mapping is done to link the new user to databases and assign them the appropriate permissions such as DB Owner. This mapping defines what databases a user can interact with.

💡Column Permissions

Column permissions in SQL Server control a user’s ability to view or modify specific columns in a table. The video demonstrates how column permissions can be set to deny access to sensitive data like social security numbers while allowing access to other data in the same table.

💡Deny

Deny is a permission setting in SQL Server that explicitly prevents a user from performing a specific action. In the video, the 'deny' permission is applied to certain tables and columns to ensure that the user 'Bobby' cannot view or alter specific data, even though he has access to other parts of the database.

💡Select Permission

The Select permission allows a user to read or view data from a table or specific columns in SQL Server. In the video, this permission is granted to 'Bobby' for certain columns in a table while being denied for others. This shows how permissions can be fine-tuned to control what data is accessible.

💡Login

In SQL Server, a login is the account used to authenticate a user and grant them access to the server. The video starts with the creation of a login for a user, assigning a username and password. Logins can be linked to database users and given permissions to access various resources within SQL Server.

Highlights

Permissions in SQL Server help control access to tables, data, and other database elements.

Creating users in SQL Server involves expanding the 'Security' section and selecting 'Logins'.

SQL Server authentication is commonly used when setting up a new login.

To create a user, assign a name and password, and optionally uncheck the enforcement boxes for simplicity.

User mapping is necessary to grant access to specific databases after creating a user.

You can assign database roles like 'DB Owner' to users to manage access levels.

It’s possible to restrict access to specific tables, not just entire databases.

Permissions for specific tables can be adjusted through the properties of the table and the permissions tab.

Users can be restricted from viewing specific columns or fields within a table.

SQL Server permissions allow for very granular control, including denying access to certain fields but allowing access to others.

Testing the permissions after setting them up ensures the right users can only see the allowed tables or fields.

To deny table access for a user, you use the 'Permissions' tab in the table properties and select 'Deny'.

The process of assigning permissions to fields involves selecting 'Column Permissions' to specify which fields are visible.

When users attempt to access restricted tables or fields, they will receive a 'permission denied' error message.

Granular permissions allow SQL Server administrators to fine-tune user access down to individual fields, making it versatile for security control.

Transcripts

okay I want to talk about permissions in

SQL

Server permissions is a pretty complex

topic and I won't be able to get into

everything regarding it but I just want

to go over a few things a few principles

regarding

permissions so basically permissions can

be used to keep users from looking at

certain tables or or altering data in

certain tables things of that

nature so what I want to show you how to

do is set up some users and then assign

some or restrict some permissions for

them so first part is creating the user

so I got management Studio running

here um to create a user it's going to

be under this security area so usually

when we open management Studio we go

into databases and start looking there

but for the users we want to expand

security and they're actually called

logins so I'm going to to create a user

I'm going to right click on

logins and do new

login there's two or three things we

need to do here we need to give them a

name so I'm just going to do a simple

name here that'll be my

user more than likely we want to choose

SQL Server authentication so we want to

click this radio

button let's assign him a real simple

password I'm just going to do 1 two 3 4

and in the production environment we

might enforce these but just for

Simplicity sake let's uncheck these

boxes so I gave my name I chose this

gave a password and unchecked

that one more thing we need to do to

create this user is give them some kind

of permissions otherwise they'll be kind

of useless to have user to do that on

this dialogue

go to user mapping over

[Music]

here and then you would give them access

to a database so just to do this example

I'm going to give them access to a

couple databases

here and again we could get into some

more advanced uh principles here but

just for Simplicity sake I'm just going

to click the database and then give them

DB owner access

I'm going to give them access to this

database

also right so user mapping give them

some access to a database then you can

hit okay to create the

user that's the first step now the

second step

is assigning some specific permissions

or maybe restricting some so again for

this we're not going to get into detail

on everything but I want I want to show

you a couple examples one would be let's

say for this

user we only want that person to have

access to certain tables not all the

tables in those databases and the second

example I'll get to will be maybe even

restricting access on a specific field

or column but let's let's restrict the

users's access to a particular table so

we've created the user and I've given

them access to uh this database down

here but let's look in that

database I've got a couple tables in

this database so let's say for the sake

argument we wanted them to see this

table but not that table so the way to

do that right now they can see both

because I gave them access to this

database so if I want not to be able to

view this table I'm going to drill down

into the database find that table and

right click on it and go to

properties so this is still assigning

permissions we've created a user we've

given them some permissions but this is

assigning more specific

permissions so I right clicked here's

the property page for that

table and I'm going to click this

permissions tab over here on the

properties and ignore this user uh we're

going to do a different user and do some

stuff with that so in your when you do

this you may not have any users here

that's fine so I clicked

permissions and what you want to do is

for the users do this search and find

that user and sort of add them and then

you can assign

permissions so I'm going to click uh

this search button here

yeah and the easiest thing to do from

this dialogue is to hit

browse and your user should show up if

he has access to this database so let's

select Bobby hit

okay and hit okay on

this what I want to do again for this

table I don't want this person to have

permissions so what we're actually doing

here is sort of withdrawing the

permissions to to this particular table

so I'm going to click on Bobby and I'm

going to check deny in all these

boxes and then hit

okay so we'll test this in a minute but

now if we were to log in as Bobby he

should still be able to see this table

but he shouldn't be able to see this one

or view the data in it based on those

permissions we just assign and sort of

withdrew second example I want to show

you which

is could be pretty useful is

restricting a user or a group of users

permission to a particular field so

maybe we're allow to see a table but we

don't want to allow them to see a

specific

field in that table just all the other

fields so I'm going to show you how to

do that once again it'll be done similar

what we just did so I'm going to go into

this database and find the

tables and for the sake argument I'm

going to say I want the user to be able

to

see uh a couple of the fields in this

table but not a specific one like a so

Security

number so once again right click on the

table go to

properties pretty much the same thing we

just did but uh there'll be one

important difference so I'm going to

click print

permissions search for

Bobby so hit browse

here check that box hit

Okay so this time

around what I'm going to do is I'm going

to assign a select permission which

basically means Bobby can select or view

the data in the table

so the way the way to do this click on

select down here and when we do this

this button becomes usable so notice it

might be grayed out but if I click

select here now we're going to actually

assign some pretty specific permissions

so I'm going to click this column

permissions and I'm going to say Grant

on these two

Fields but not on this one so I'm going

to say deny on this one all right so I

checked grant for the fields I want

Bobby to be able to see review and deny

on the ones that I don't want them so

they could be two or three Fields each

depending on what table and Fields you

have hit okay on

that and that's it just hit okay on this

as

well so that's it really what I've just

shown you is creating a user

um assigning some permissions to the

user and then restricting some

permissions for particular tables or for

particular fields or columns in a

table but just so you see how this works

so feel free to look back at that let's

just see how this works we just show you

what happens if we log in as

Bob hit disconnect here

and I'm going to log back in as

Bobby just so we can test this

out and see if his permissions uh got

taken away or if he can see certain

things and not other

things so let's test that first one

first let's go in and see if he can look

at one of those tables but not the

other so if he clicks on this table and

tries to look at

it notice gives a permission denied

error so basically they that person that

particular user can't view that table

but notice he should be able to still

see this

one right so we've assigned some

permissions to restrict certain stuff in

our database let's try that other one

other one out just so you can see how it

works

so let me go into this T

database and run a quick

query just for the sake of speed I'm

just going to let the

the thing create the query for me and

then I'll modify it a little

bit so right now it's trying to look in

that table at all three fields and it's

getting and access denied so what I'm

going to do is modify this to say okay

since this is Bobby maybe he is just

going to be able to see these two Fields

but not that third one so let's try this

and notice now for this particular user

they can see certain Fields but not

others based on the restri uh

permissions that we applied