Monday, January 30, 2012

Overview of Logins in SQL Server 2008



Logins is the major object in SQL Server and it’s a first security step to secure your database Server with granted users. There are many security objects Depending on the login object. The following is an overview of how to creating logins in SQL Server and some basic knowledge about security principals.

• Right click on Logins folder that exists in the Security Folder.


• and then select New Login

• the Login – new form will appear


• General is the first tab in the Login – new form that need Login name

o Login name option may be one of the following.

 Existing Windows user account that found in the same server of SQL Server or in the another one according with network capability and some administration option. The user account in this case not needs a password because SQL Server will ask the operating system that hosts this account for that password.



 New user account that create in the first time inside SQL Server. The user account in this case needs a user name and password.


• Server Roles tab contains all fixed server groups in the SQL Server. Each group has set of server permissions. By default the new login must a member in the public server role. And also this new login may include in other server roles. It’s mean that this new login have all permission of assigned server roles. for more details about fixed server roles please click here .


• In User Mapping tab, assign all the databases for new login. SQL Server will create user for each database assigned to targeted new login. for example if administrator create new login and assign three Databases for that login, it’s mean SQL Server has four object the first object for the login created and other three user object for each database assigned to this login .


o Also in the User Mapping tab, new login may include in one or more database role membership. Database role membership is a set of groups inside each database created. Each group in the database has a set of permissions in the database scope.


By default, the new login must a member in the public database role in case that this login mapped in specific database. Administrator can assign another roles for a login created.

• Securables tab is one that gives permission for a new login. But what is Securable mean. Its mean all SQL Server objects that will assigned for a specific login with specific permission.
In Securables tab, the Securable objects that allowed here is one of the following types:

o Servers

o Endpoints

o Logins




To select a specific Securable,

o Click on Search … Button.

o Add Objects form will appear.




o Select one of the above options to specify the object type wanted. And then press OK button.
When all selected Securable object appear In the Securables table, then Select one of them from table to specify all permission needed for that securable from Permissions table.

Each of Securable objects has a set of permissions in the permissions table. Each permission may has Grant, with grant or deny permission.
• The last tab in the Login – New form, is Status tab. This tab specify the following :

o Permission to connect to database engine

o Login status



No comments:

Follow by Email