What is The Public Server Role in SQL Server

In the previous article “How To Create SQL Server Login“, we just seen how to create SQL Server Login Using SSMS Graphical User Interface but did not assigned any Server Role except public which is assigned to every login by default and can’t be changed.

So, as I discussed previously we only learned how to create SQL Server Login. Every new SQL Server Login is assigned by a default server role ‘public’. This public server role is fixed server role and is built-in role of SQL Server.

Here we will check public role on LoginDemo which we already created in the previous article “How To Create SQL Server Login“. To view the assigned role follow the following steps:

Step-I: Open SQL Server Management Studio and click on Connect Button.

Step-II: Expand Security Tab and then Login tab by clicking on the + icon.

Step-III: Right click on the Login which you created and click on Properties.

A Login window will appear as shown below.

Step-IV: Click on Server Roles, A list of checkboxes with Server role will be shown where public Server Role is checked as below.

Nice, We created a login with a default public server role. Let’s see how to login the SSMS with LoginDemo and what we can do with this server role.

How to Connect with LoginDemo Server Login

Step-I: Open SQL Server Management Studio.

Step-II: Provide Server name, Choose SQL Server Authentication (Because we chosen SQL Server Authentication at the time of Login Creation), Select Login, Put your password, and click on Connect button as shown below.

An SQL Server Instance will be open with login credentials which we provided as below.

Since we created a login LoginDemo with public Server role only and didn’t associated this login with any database and database role, so we can’t work on any database like, SELECT, INSERT, UPDATE, DELETE etc. Even we can’t select the database name in the context menu.

If we Try to create a new database, we get an error like below.

How to Import MS Access (.mdb) to SQL Database Earlier in How to Export SQL Server Table to Access Database post we learned how to export SQL Server table to Access Database using SQL Server Management Studio Graphical User Interface. Now in this article, we will learn how to import table from MS Access database to […]

Tags: public server role, server role