Creating new SQL login accounts is a routine task that anyone administering a database will do quite frequently. Knowing how to create the account and assign the appropriate role memberships is important in maintaining tight security controls across your SQL environment. I’ve seen numerous cases where blanket permissions are provided to user logins. This can create a huge mess when auditing the security and allow users to perform tasks they shouldn’t be doing. In short, knowing what access your users have is extremely important and setting them up correctly is the first step to doing this.
The example outlined below demonstrates creating a service account named
svc_LinkedSvr that might be used to assign credentials to a linked server.
The first step in creating a new SQL Login is to connect to the SQL server where you wish to create the login. Once connected, expand the Security module, right click Logins and select New Login…
This will display the New Login – General window. For the purposes of this overview I will be creating a SQL Server Authentication login, as opposed to a Windows Authentication login. The differences between creating each are minor but in order to create a Windows Authentication login you would generally need to have a domain level account created first.
The first thing you need to do on the General page of the New Login window is provide the Login Name. As shown below, I am calling it
svc_LinkedSvr. By default, Windows authentication will be selected. Change this by clicking the radio button next to SQL Server authentication. Once selected some new fields will be available.
Enter a password for the new login. Also, by default, the options to Enforce password policy, Enforce password expiration and User must change password at next login will be checked. It is generally good practice to leave these selected for true user accounts, though depending on the purpose of the account and the rules put in place by your IT team it may not be necessary to keep these all checked. However, if you don’t know then leave them all checked.
You can also specify a default database for the account. In most cases you can leave this as master. If you were to select a specific database then each time the user opens a query window while connected to that specific server it would connect them to the default database that was assigned here.
The next step is to review the Server Roles page. By default only the
public role will be checked. Unless you are creating an account with elevated permissions, no additional roles should be selected.
Now it is time to set up the User Mapping for each database on the server that you want the account to have access to. Click on the User Mapping page. This page displays all the databases available on the server. Click the checkbox next to each database that you want the login to have access to. At the bottom of the window you will see the individual database role memberships for the selected database. Generally you will only want to provide
db datawriter and
public roles to the login. However, if you are creating a login with elevated permissions you will need to select additional roles.
In the example above I am assigning role membership to the AdventureWorks2012 database only.
In most cases there is no need to make any modifications to the Securables page or Status page.
With that said, if you are creating the login but do not want it to be able to connect to the database engine yet or simply want it be disabled, you can do this from the Status page.
Once you have reviewed all the options and made any desired changes, click OK to create the account.
You can then expand the Logins drop-down under Security and find the newly created login. If you haven’t refreshed the server connection it should be located at the very bottom of the list.
Finally, as with most tasks in SQL Server, you can also create login accounts by scripting them out. The script below will create the same login as outlined above. Notice in the
CREATE LOGIN code block that I am accounting for the items included on the General page that set the default database as well as enforce the password policy and expiration.
CREATE LOGIN [svc_LinkedSvr] WITH PASSWORD='123456' MUST_CHANGE,
CREATE USER [svc_LinkedSvr] FOR LOGIN [svc_LinkedSvr]
ALTER ROLE [db_datareader] ADD MEMBER [svc_LinkedSvr]
ALTER ROLE [db_datawriter] ADD MEMBER [svc_LinkedSvr]
This script can easily be modified to provide role memberships to additional databases should it be necessary.