Join Adam Wilbert for an in-depth discussion in this video Creating and using a guest account, part of Database Foundations: Administration.
- The "guest" user account can be enabled on any database, and applies a base set of permissions to any user of the server even if they haven't explicitly been granted permission to access the database. Enabling the guest account is often considered a high security risk since anyone with access to the server can assume those permissions. Let's see how this works. I'm currently logged back in as my system administrator account, and if I go into the H+ Active database under Security and then Users, you'll notice that we have this guest account right here. Right now the icon has this red downward-pointing arrow which means that is currently disabled.
In order to enable the accounts, I'm going to start a new query here and we're going to execute a stored procedure. I'll type in the keyword EXEC for execute. The name of the stored procedure is sp_adduser, and then the name of the user is going to be in single quotes, and its just 'Guest'. Let's go ahead and execute this query and we see the command was completed successfully, and then if we come over to the Users folder. Right click and choose "Refresh". We'll see that the guest account no longer has that downward-pointing, red arrow.
So now this guest account is active. Next, let's go ahead and assign some permissions to the guest account. I'll close this query window right here, and I don't need to save any changes. Then, I'll double click on the guest account here in the Object Explorer. Then, in the Securables page here, under Securables, I'll press the search button to add in a new securable to the guest account. I'll choose to search by "Specific objects" and say OK. I'll press Object Types and then choose our "Tables" objects We'll press OK to that. Then, I'll browse through the different tables. Then, we'll add in access to our "Customers" table.
So, I'll place a check mark here and press OK. We'll press OK again, and that'll return us back here to the securables section. Then, I want to grant in a Select permission on the "Customers" table, so down here underneath the Permissions, I'll find the "Select" row, and I will grant that permission on the "Customers" table to the guest account. Once I've made that change, I'll press the OK button here, and that'll return me back to the Object Explorer. Next, let's create a new user on the server. So, not a user of the database, but a user of the server itself. I'll come into this Security folder here, and I'll expand it and go into the Logins folder.
Then, I can right click on it, and choose "New login". We're going to create a new SQL server authentication login, and I'm just going to call it "Temp" for now. I'll give it a password of "temp" which is a terrible password choice. Feel free to choose something that's more secure if you like. I'm going to turn off this check box here that says "Enforce password policy", and that's all I need to do to create this user. I don't want to come into the User Mapping and attach them to the H+ Active database, and I don't want to assign any Securables to this particular user. Let's go ahead just leave all those in their default state, and I'll press OK.
Next, I'll log off of the server, and then I'll log back in as that new "temp" account. So, I'll change the authentication here to "SQL Server Authentication". The Login name is "temp". The Password is also "temp" right now, and I'll press "Connect". So, now I'm logged into the temp account. If I go into the Databases folder and expand the H+ Active database, I can come into the Table section, and you can see that, even though I'm logged in as "temp" and I didn't give myself any permissions to access this particular database, I do have access to that "Customers" table, and that's because I've adopted the permissions that were assigned to that guest account.
So, if I right click on Customers and say "Select Top 1000 Rows", you can see that I have access to all of that data. Let's go ahead and close that tab down. So, as you can see, the guest account has some powerful and potentially dangerous capabilities. Because of the way the guest account applies permissions to everyone on the server, the best practice is to leave it disabled unless you absolutely need it. In order to do that, we need a log back in as the System Administrator, so I'll log out of the server here, and then log back in. We'll change our Authentication mode back to "Windows Authentication", and connect as the SysAdmin.
Then, I'll go into the databases, I'll choose the H+ Active database, and I'll choose the New Query window. The line of code that I need is REVOKE. And then I'm going to revoke the "connect" permission from the "guest" user account. So, I'll type in the line "REVOKE connect FROM guest". When I execute that, it says it was completed successfully, and then if I go back into that Users folder in the Security folder here, find Users, and you'll see that guest account has that red, downward-pointing arrow again which indicates it has been disabled.
So now that we've disabled the guest account, we aren't accidentally revealing sensitive information to the entire pool of users on the server.
Note: This course will also prepare certification candidates for the "Administer a database" domain of the Microsoft Technology Associate (MTA) Exam 98-364, Database Administration Fundamentals.
- Securing the database server
- Understanding Windows authentication vs. SQL Server authentication
- Assigning fixed server roles and fixed database roles
- Granting object-level permissions
- Understanding ownership claims
- Creating backups
- Restoring a database