If your database server is a managed instance or SQL Server running on a VM or on-premise, you are not limited to SQL statements to assign permissions to roles and roles to users. Within SQL Server management studio, there are graphical tools or a graphical interface that lets you perform the same tasks.

I’m going to start by creating a new login called whome. I’ll make this an SQL Server login and give it a very simple password. The password is so simple. In fact that I will uncheck in force password policy because it will not pass the validation.

Next, I’m going to make the default database for this user, the lorrylog database. I won’t assign any server roles, and in terms of user mapping, I will map them directly to the lorrylog database giving them the default scheme of dbo.

You can notice that the db_owner role has been automatically assigned when I assigned them to this database. I’ll just uncheck that. And give them the data reader and data writer roles and click okay. If I go into the lorrylog database and look under security and users, we can see the new user sitting in there.

That user was created from the login when I selected the lorrylog database and going into membership; we can see the data reader and data writer roles. Now I’m still logged in as the SA user so, what I’ll do is create a new table that will be imaginatively named NewTable. This is a very simple table is just an ID and a varchar field. I’ll also insert one record into the new table.

Now I’m going to go to the new table and set permissions to deny the whome user from inserting records into that table. So, right-click on the table, select properties, go to permissions, and first of all, search uses or roles for the whome user. Under permissions, I will find the insert permission and check the deny box.

As well as the grant and deny permissions, there is also the with grant permission. This means that a user not only has that permission, but they have the ability to delegate that permission to another user or role. Now I’m going to log into the server as the whome user, and we can see that I’m not permitted to view any of the other databases.

Now when I go into the lorrylog database, I’ll open a new query window. Because whome is part of the data reader role, I’ll do a select on the new table. Sure enough, there is the data that we previously inserted. Now I’ll try and insert another record into NewTable, and as we would expect, permission is denied on doing the insert. However, when I do an update, that is successful.

 

As I said earlier, we can set permissions on almost any object within a database, and that includes columns, so what I’ll do now is go and set select permissions on the new table by going back into permissions under properties select my who me user so I’m going to deny select on the ID column, thereby hiding it.

So, the first thing we have two deny select permission, then the column permissions button becomes enabled, and from there, we can specify which column, we want to deny or grant permission on, so we click okay and okay. We do a select *, and sure enough, we don’t have select permission on the ID column. So then we actually have to specify the columns that we do have permission on, and in this case, it’s just name.