SQL Server: User Roles. Adding Database Users SQL Server Security and Trusted Authentication

Creating SQL Server 2005 database users, CREATE USER, user properties

You can create a database user:

q on the graphical screen from the container Database_name | Security |Users in Management Studio;

q using the command CREATE USER(stored procedure sp_ adduser, which was used for this purpose in previous versions of SQL Server, is retained for backward compatibility only). For example, the command to create a user User1, which will correspond to the SQL Server login Login1 with default scheme dbo, might look like this:

CREATE USER User1 FOR LOGIN Login1 WITH DEFAULT_SCHEMA = dbo;

When creating a user you will need to specify:

q Username(User name), to which the same rules apply as for other SQL Server objects;

q login(SQL Server or Windows) which will be assigned to the user of this database. Once a user is created, the login assigned to him cannot be changed. You can create a user to whom no login will be assigned (using the switch Withoutlogin). Such a user will no longer be able to assign a login. Users of this type - without logins - are used only for additional security configuration in Service Broker. Note also that if a login has already been assigned to a user, then it cannot be assigned to another user at the same time;

q certificate(Certificate name) or asymmetric key(Key name);

q default scheme(Default schema);

q for which schemes this user will be owner(Owned schemas);

q what database roles(Database roles) will be assigned to it.

There are only two required parameters - username and login.

On the tab Securables The user can be immediately granted permissions to database objects. Permissions will be discussed in the following sections. Tab ExtendedProperties allows you to define additional custom properties for this object. They are used for the same purposes as advanced database properties. (see section 4.8).

Changing user properties and deleting them is done from the same container in Management Studio as creating the user, as well as using commands ALTER USER/DROP USER. You cannot delete a user who owns any objects in the database.

5 answers

Based on your question, I think you may be a little confused about the difference between user and login. A login is an account on SQL Server in general - someone who can log into the server and who has a password. A user is an input with access to a specific database.

Creating a login is easy and should (obviously) be done before creating a user account to log into a specific database:

CREATE LOGIN NewAdminName WITH PASSWORD = "ABCD" GO

This is how you create a user with db_owner privileges using the Login you just declared:

Use YourDatabase; GO IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N"NewAdminName") BEGIN CREATE USER FOR LOGIN EXEC sp_addrolemember N"db_owner", N"NewAdminName" END; GO

Now the logins are a little more fluid than I seem to be above. For example, a user account is automatically created (in most SQL Server installations) for the Windows Administrator account when the database is installed. In most situations I just use this when I'm managing the database (it has all the privileges).

However, if you are going to access SQL Server from an application, you need to configure the server for "Mixed Mode" (both Windows and SQL Login) and create a Login as shown above. You will then receive "GRANT" priviliges for that SQL input based on what is needed for your application. See for more information.

UPDATE: Aaron points out using sp_addsrvrolemember to assign a provisioned role to your account. This is a good idea - faster and easier than granting privileges manually. If you find it, you will see many links. However, you should still understand the difference between a login and a user.

Full administrator rights for the entire server or a specific database? I think the others answered for the database, but for the server:

USE ; GO CREATE LOGIN MyNewAdminUser WITH PASSWORD = N"abcd", CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF; GO EXEC sp_addsrvrolemember @loginame = N"MyNewAdminUser", @rolename = N"sysadmin";

You may need to leave out the CHECK_ parameters depending on which version of SQL Server Express you're using (it's almost always a good idea to include this information in your question).

If you want to create a generic script, you can do it with an Execute statement, replacing with your username and database name

Declare @userName as varchar(50); Declare @defaultDataBaseName as varchar(50); Declare @LoginCreationScript as varchar(max); Declare @UserCreationScript as varchar(max); Declare @TempUserCreationScript as varchar(max); set @defaultDataBaseName = "data1"; set @userName = "domain\userName"; set @LoginCreationScript ="CREATE LOGIN [(userName)] FROM WINDOWS WITH DEFAULT_DATABASE =(dataBaseName)" set @UserCreationScript =" USE (dataBaseName) CREATE User [(userName)] for LOGIN [(userName)]; EXEC sp_addrolemember ""db_datareader "", ""(userName)""; EXEC sp_addrolemember ""db_datawriter"", ""(userName)""; Grant Execute on Schema:: dbo TO [(userName)];" /*Login creation*/ set @LoginCreationScript=Replace(Replace(@LoginCreationScript, "(userName)", @userName), "(dataBaseName)", @defaultDataBaseName) set @UserCreationScript =Replace(@UserCreationScript, "(userName)" , @userName) Execute(@LoginCreationScript) /*User creation and role assignment*/ set @TempUserCreationScript =Replace(@UserCreationScript, "(dataBaseName)", @defaultDataBaseName) Execute(@TempUserCreationScript) set @TempUserCreationScript =Replace(@UserCreationScript, "(dataBaseName)", "db2") Execute(@TempUserCreationScript) set @TempUserCreationScript =Replace(@UserCreationScript, "(dataBaseName)", "db3") Execute(@TempUserCreationScript)

You can use:

CREATE LOGIN WITH PASSWORD = " " ; GO

You can also use:

GRANT permission [ ,...n ] ON SCHEMA:: schema_name

To configure permissions for the schema you have assigned to users.

Last week I installed Microsoft SQL Server 2014 Developer Edition on my dev page and immediately ran into an issue that I've never seen before.

I've installed different versions of SQL Server countless times and it's usually a painless process. Install the server, launch the management console, it's that simple. However, after completing this installation, when I tried to log into the server using SSMS, I got an error similar to the one below:

SQL Server Login Error 18456 "Login failed for user... (Microsoft SQL Server, error: 18456)" I've used this error if I entered the wrong password when logging in - but that's only if I'm using mixed mode (Windows and SQL Authentication). In this case, the server was configured with Windows Authentication only and the user account was mine. Im still not sure why it didn't add my user to the SYSADMIN role during installation; maybe I missed a step and forgot to add it. In any case, not all hope was lost.

The way to fix this if you are unable to login with any other account on SQL Server is to add Network Login via CLI. To do this, you need to be a Windows administrator for the PC you are logged into.

Stop MSSQL service. Open a command prompt using the Run as administrator command. Go to the folder where the SQL Server EXE file is stored; The default for SQL Server 2014 is "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn". Run the following command: "sqlservr.exe -m". This will start SQL Server in single user mode. Once you exit that command prompt, open another one, repeat steps 2 and 3. In the second command prompt window, run "SQLCMD -S Server_Name\Instance_Name" In that window, run the following lines, pressing Enter after each one: 1

CREATE LOGIN [domainname\username] FROM WINDOWS 2 GO 3 SP_ADDSRVROLEMEMBER "LOGIN_NAME", "SYSADMIN" 4 GO Use CTRL + C to terminate both processes in command prompt windows; you will be prompted to press Y to end the SQL Server process.

Restart the MSSQL service. This is it! You can now log in using your online login.

12/07/2016 Tim Ford

What a Database Administrator Should Know About Security: An Explanation of Terms and a General Overview of Objects in Practice

SQL Server Security and Trusted Authentication

There are two types of security schemes in Microsoft SQL Server: SQL Server security and trusted authentication (also known as Windows authentication). SQL Server security is a standard login username and password combination, and trusted authentication assumes that the device that is trying to connect to the instance of SQL Server is approved by the domain authentication procedure, and the results of this verification are passed to the instance of SQL Server: the domain is assumed to be which hosts the SQL Server instance trusts the user account - check performed previously.

Users and applications that attempt to connect to SQL Server through the SQL Server security mechanism must provide a username and password created on the target SQL Server. When connecting using trusted authentication, you only need to set a flag in the connection string to indicate that trusted authentication is used. In this case, the end user or application service account credentials are sent as a security token. If there is a matching username on SQL Server, then access will be granted.

Names and users

There are two levels of access to a SQL Server instance: server (or instance) user accounts and database users. Using accounts, servers allow an external user (in this article, the term "user" refers to any application, service, API, etc. attempting to connect to SQL Server) to make the initial connection to an instance of SQL Server. With SQL-based security, this requires a username and password. In the case of trusted authentication, this is a domain account.

There are two ways to create these user accounts: using Transact-SQL (https://msdn.microsoft.com/en-us/library/ms189751.aspx? f=255&MSPPError=-2147217396) or through the GUI. The procedure for using T-SQL to create accounts is well documented, and it is best to refer to the official Microsoft SQL Server documentation. For now, let's look at how to create an account in the graphical interface. To launch the dialog box to create user accounts, connect to the instance of SQL Server in SQL Server Management Studio (SSMS) in Object Explorer, and then expand the Security\Logins node. Right-click Logins and select New Login from the context menu (Figure 1).

You'll see the account settings dialog shown in Figure 2. You can change your username in the same window.

This is the General tab for creating (and changing) account settings. It differs from the two previously described security schemes. On the General tab you can set:

• Login name. Used for authentication. In the case of Windows, or trusted authentication, you must specify the name in the format DOMAIN\LOGIN, where LOGIN is the name of the user within the domain from which the user is authenticating. If the SQL Server instance is located in a different domain, then a trust relationship is required between that domain and the SQL Server domain.
• Password. With SQL Server authentication, the password text box is enabled and you enter both the username and the associated password.
• Password Policy and Expiration. The password and expiration policy check boxes are also selected in SQL Server authentication mode, and the policies that are in effect in Active Directory in the domain that hosts SQL Server are applied. By assigning a SQL Server username, you can allow users to change their passwords after registration. As a result, the database administrator is denied access to the end user's account name.
• Certificates, Keys, Credentials. In this beginner's article, we won't cover certificates, keys, and credentials.
• Default Database Once the connection to SQL Server is established, two steps are performed: authentication (a username must exist for the user's domain credentials if Windows or Trusted authentication is used, or the username/password combination must be passed to the SQL Server instance). This is the first barrier. The second is that the verified username has an associated user object in the default database - the database initially configured as the username context after identity verification. Even if the first hurdle is passed, if there is no corresponding database user in the default database, the connection will not be established and the corresponding entry will be written to the SQL error log. But there are exceptions: if a user's server role is so important that it is necessary to set implicit rights for it by default in each database, then it is not necessary to have a corresponding user in the default database. However, I'm getting ahead of myself because we haven't covered database users or server roles yet. Suffice it to note that when you select a default database in the GUI, no associated database user is created. You simply specify what the default database should be. When you do this, you use the User Mapping tab of the Create Login dialog box to create an associated database user.

Let's move on to the next tab, Server Roles, shown in Figure 3. On this page, you can select any SQL Server (instance) level roles for the new user. Server roles are collections of rights, also known as securables, that are packaged into a collection so that you don't have to assign rights to each securable individually. By default, each account is a member of a public role, which allows you to establish a primary connection to an instance of SQL Server. The rest of the article will look at each server role included in Microsoft SQL Server.

The next page of the Create Login dialog box in SQL Server Management Studio is for user account mapping. Each account can have a user in one or more databases. On this page you can create database users associated with the new account. To do this you need to provide the following information.

• Database. Select the check box next to the database where you want to create an associated user for the account.
• User Name The user object name does not necessarily match the account name, and we'll show you how this can be changed later.
• Default Schema Each database user must be assigned to a default schema. A schema is a collection of database objects that are separated logically (but not necessarily physically) from other objects in the database. You can grant a user or group of users rights to all objects in a given schema, for example, grant all users from Accounting (or an accounting application service account) certain rights to all objects in the Billing schema, but not grant access to those objects to other users. When you assign a default schema to a database user, there is no need to include the schema name in T-SQL calls to the database when addressing objects in that schema. This also means that if a user is granted rights to create objects, they will be created in this schema by default unless you specify a schema name when creating the objects. Later in the article we will touch on the concept of circuits.
• Database Role Membership Just like at the instance or server level, each database has a predefined collection of rights packaged into roles. We'll look at the database roles that come with Microsoft SQL Server a little later.

Let's take a look at the example dialog box for the SQLCRUISE\skipper user account (see Figure 4).

In this example, the user SQLCRUISE\skipper is granted rights to the default database (lifeboat), where the associated username is simply skipper. The default scheme is skipper_only. The other two databases in which users will be created for this account use the same user name as in the username (usually for ease of identification), and the default schema is dbo, which is the default in Microsoft SQL Server for all user-defined objects. More information about this will be provided in the next section. In the case of the lifeboat database, we only provide membership in the public database role, which allows you to connect to the database without additional permissions.

The next page, Securables, presents securables at the server or instance level. As noted above, securables are permissions granted to objects. Securable objects are typically provided in the following cases:

• the fixed role is too broad (many other rights for the account);
• The assigned role or set of roles does not fully cover all the rights required for the account.

In our example, I granted SQLCRUISE\skipper membership in the public server role and allowed it to view any object definitions that exist at the server level (see Figure 5).

Finally, we go to the Status page. On this page you can allow or deny access for the user (by default Grant is selected - allow). Therefore, you can create an account, grant rights, create associated users, and then revoke access. You can return to this window for an existing user and revoke access to the SQL Server instance. Enabling and disabling an account occurs in the same way (see Screen 6). Finally, we can view the user's account status and see if the account has been locked due to too many failed login attempts with an incorrect password.

Each option works successfully if there is only one table named tblFoo in the SQL_Cruise database and the current database context was the SQL_Cruise database. However, only the first option will work correctly, regardless of which database you are currently connected to on the SQL Server instance containing the SQL_Cruise database. The second option will be executed if you are connected to the SQL_Cruise database, regardless of the number of schemas that have tblFoo, since you specified the dbo schema. The third option will generate an error message (see Figure 8) if the SQL_Cruise database has multiple schemas with tblFoo, as shown in Listing 4, where I created both the dbo.tblFoo table and the user.tblFoo table.

Yes, that's right - the object exists, but you get the error message Invalid object name. Never be sure in advance that an object with that name does not exist. The message may indicate a syntax problem.

Fixed roles are included with Microsoft SQL Server at both the server and database levels. However, you can create your own roles if situations arise in which you need to assign the same permissions to many users. Creating custom roles allows you to define these rights only once: when you create the role, not on a per-user or per-user login account basis (depending on database or server roles). In addition to saving time, it eliminates inconsistency when assigning rights to multiple users or accounts.

Use the hyperlinks to navigate through the complete list of Microsoft-provided server roles and database roles. Future articles, as we begin to move from the basics to deeper topics, will cover how to create roles, add users or accounts to those roles, and associate rights with roles at the server and database levels.

Microsoft SQL Server security is a very important topic. It is distinguished by its depth, as well as the originality of its terminology. I hope I have achieved my goal of explaining the various terms and giving a general overview of the objects used in practice. We'll cover a few more topics in these entry-level articles, but I'll soon address more complex issues that arise from this post. As always, I thank readers for their attention and look forward to your comments. I hope this article will help newbie database administrators master the mysteries of SQL.

Listing 1. Code corresponding to the settings made in the GUI

USE GO CREATE LOGIN FROM WINDOWS WITH DEFAULT_DATABASE= GO USE GO CREATE USER FOR LOGIN ALTER USER WITH DEFAULT_SCHEMA= GO CREATE SCHEMA AUTHORIZATION GO USE GO CREATE USER FOR LOGIN ALTER USER WITH DEFAULT_SCHEMA= GO USE GO CREATE USER FOR LOGIN ALTER USER WITH DEFAULT_SCHEMA = GO use GO GRANT VIEW ANY DEFINITION TO GO

Listing 2. Information about system and database users

SELECT name , sid , principal_id , type_desc , default_database_name FROM sys.server_principals WHERE name = "professor"; SELECT name , sid , principal_id , type_desc , default_schema_name FROM lifeboat.sys.database_principals WHERE name = "professor";

Listing 3. Example query for selecting table columns and rows

OPTION 1: FQDN -========================================================= ============================= SELECT * FROM SQL_Cruise.dbo.tblFoo; -==================================================== ======================== - OPTION 2: name defined via schema -================ ===================================================== ======= SELECT * FROM dbo.tblFoo; -==================================================== ======================== - OPTION 3: object name only -================== ===================================================== ===== SELECT * FROM tblFoo; Listing 4. Creating tables with multiple schemas USE GO CREATE SCHEMA AUTHORIZATION GO CREATE TABLE dbo.tblFoo (id INT); CREATE TABLE .tblFoo (id INT); SELECT * FROM tblFoo;

SQL Server Security Basics

Adding Database Users

Database sources

The user can log on to the database system using a Windows user account or a SQL Server login. To subsequently access and work with a specific database, the user must also have a database user account. To work with each individual database, you must have a user account specifically for that database. A database user account can be mapped to an existing Windows user account, a Windows group (in which the user has membership), login, or role.

You can manage database users using Management Studio or Transact-SQL statements. Both of these methods are discussed in the following subsections.

Managing Database Users Using Management Studio

To add a database user using Management Studio, expand the server node in the Object Explorer window and the "Databases" folder in it, and in this folder expand the desired database node and the "Security" folder in it. Right-click the Users folder and select New User from the context menu. The Database User - New dialog box will open, in which you must enter the User name and select the appropriate Login name:

Here you can also select the default scheme for this user.

Managing Database Security Using Transact-SQL Statements

To add a user to the current database use CREATE USER instruction. The syntax for this instruction looks like this:

CREATE USER user_name Syntax conventions

The user_name parameter specifies the name by which the user is identified in the database, and the login parameter specifies the login name for which the user is created. The cert_name and key_name parameters specify the corresponding certificate and asymmetric key, respectively. Finally, in the parameter WITH DEFAULT_SCHEMA specifies the first schema with which the database server will begin searching to resolve object names for a given database user.

The use of the CREATE USER statement is shown in the example below:

USE SampleDb; CREATE USER Vasya FOR LOGIN Vasya; CREATE USER Alex FOR LOGIN WITH DEFAULT_SCHEMA = poco;

To successfully run the second instruction in the example on your computer, you must first create a Windows account for the user Alexandr and specify the name of your server instead of the ProfessorWeb domain (server).

In this example, the first CREATE USER statement creates a Vasya database user for the Vasya user of the Windows account. The default schema for user Vasya will be dbo because the DEFAULT_SCHEMA parameter has no value specified. The second CREATE USER statement creates a new Alex database user. The default schema for this user will be poco. (The DEFAULT_SCHEMA parameter can be set to a schema that does not currently exist in the database.)

Each database has its own specific users. Therefore, the CREATE USER statement must be issued for each database for which a user account must exist. Additionally, for a given database, a SQL Server login can only have one database user.

Using instructions ALTER USER you can change the database user name, change the default user schema, or override the user with a different login name. Similar to the CREATE USER statement, a user can be assigned a default schema before it is created.

To remove a user from the current database, use the statement DROP USER. A user who is the owner of protected objects (database objects) cannot be deleted.

Default Database Schemas

Each database in the system has the following default schemas.

When deploying enterprise information databases based on 1C solutions in client-server mode using the MS SQL DBMS, sometimes it is necessary for different databases to be created on behalf of different users. Those. We sometimes need to create a user other than sa in SQL Management Studio and enter his data in the fields of the window for adding a new information security. (Fig.1.)

What are the minimum rights under which this will all function?

The ITS methodological support materials state that “this user must have not only full rights to the infobase database, but also rights to create databases in the SQL server and to read Master database tables.” To see how this works in practice, we will conduct a test installation of information security in a client-server version using MS SQL Server 2008 R2 Express. You can, of course, stupidly copy the parameters of the sa user, but let's do it meaningfully, it's always useful.

Let's launch the SQL Management Studio 2008 R2 environment, establish a connection to the SQL server and open the Security->Logins section and select the “Create login” context menu command, set the user name and set the rights dbcreator, public(Fig.2)

On the user properties page “User Mapping”, check the “Schema” checkbox for all bases in the table of mapped users master, model, msdb, tempdb, and for each database from the table we note the role membership public, db_owner(Fig.3)

Now you can return to the window shown in Fig. 1 and apply the entered parameters. Click Further-> Ready and... the database has been created, the list of databases has increased by one position.

Thus, we will be able to please and reassure the system administrator, because the specified combination of MS SQL user rights is minimally sufficient for use with the 1C platform in client-server mode, and the “sa” password will remain uncompromised, and we have the MS SQL user rights we need .