用于创建新用户的 SQL Server 脚本

SQL Server Script to create a new user

提问by Ashish Ashu

I want to write a script to create a adminuser ( with abcdpassword ) in SQL Server Express. Also I want to assign this user adminfull rights.

我想编写一个脚本来在 SQL Server Express 中创建一个admin用户（带abcd密码）。我也想为这个用户分配admin完全权限。

回答by Mark Brittingham

Based on your question, I think that you may be a bit confused about the difference between a Userand a Login. A Loginis an account on the SQL Server as a whole - someone who is able to log in to the server and who has a password. A Useris a Loginwith access to a specific database.

根据您的问题，我认为您可能对User和Login之间的区别有些困惑。一个登录的是SQL Server作为一个整体上的账户-有人谁是能够登录到服务器，谁拥有一个密码。一个用户是一个登录访问到特定的数据库。

Creating a Loginis easy and must (obviously) be done before creating a Useraccount for the login in a specific database:

创建登录很容易，并且必须（显然）在为特定数据库中的登录创建用户帐户之前完成：

CREATE LOGIN NewAdminName WITH PASSWORD = 'ABCD'
GO

Here is how you create a Userwith db_owner privileges using the Loginyou just declared:

以下是使用您刚刚声明的登录名创建具有 db_owner 权限的用户的方法：

Use YourDatabase;
GO

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

Now, Loginsare a bit more fluid than I make it seem above. For example, a Login 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 that when I am administering a database (it has all privileges).

现在，登录比我在上面看起来更流畅。例如，安装数据库时，系统会自动为 Windows 管理员帐户创建登录帐户（在大多数 SQL Server 安装中）。在大多数情况下，我只在管理数据库时使用它（它具有所有权限）。

However, if you are going to be accessing the SQL Server from an application, then you will want to set the server up for "Mixed Mode" (both Windows and SQL logins) and create a Login as shown above. You'll then "GRANT" priviliges to that SQL Login based on what is needed for your app. See herefor more information.

但是，如果您要从应用程序访问 SQL Server，那么您需要将服务器设置为“混合模式”（Windows 和 SQL 登录）并创建一个登录，如上所示。然后，您将根据应用程序的需要“授予”该 SQL 登录的权限。请参阅此处了解更多信息。

UPDATE: Aaron points out the use of the sp_addsrvrolemember to assign a prepared role to your login account. This is a good idea - faster and easier than manually granting privileges. If you google it you'll see plenty of links. However, you must still understand the distinction between a login and a user.

更新：Aaron 指出使用 sp_addsrvrolemember 将准备好的角色分配给您的登录帐户。这是一个好主意 - 比手动授予权限更快、更容易。如果你谷歌它，你会看到很多链接。但是，您仍然必须了解登录名和用户之间的区别。

回答by Aaron Bertrand

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

整个服务器或特定数据库的完全管理员权限？我认为其他人回答了数据库，但回答了服务器：

USE [master];
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 off the CHECK_ parameters depending on what version of SQL Server Express you are using (it is almost alwaysuseful to include this information in your question).

根据您使用的 SQL Server Express 版本，您可能需要省略 CHECK_ 参数（在您的问题中包含此信息几乎总是有用的）。

回答by Guish

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

如果你想创建一个通用的脚本，你可以用一个 Execute 语句来做，并用你的用户名和数据库名称替换

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)

回答by Simon P Stevens

You can use:

您可以使用：

CREATE LOGIN <login name> WITH PASSWORD = '<password>' ; GO 

To create the login (See herefor more details).

创建登录（请参阅此处了解更多详细信息）。

Then you may need to use:

那么你可能需要使用：

CREATE USER user_name 

To create the user associated with the login for the specific database you want to grant them access too.

要创建与特定数据库的登录名关联的用户，您也希望授予他们访问权限。

(See herefor details)

（详情请看这里）

You can also use:

您还可以使用：

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

To set up the permissions for the schema's that you assigned the users to.

为您分配给用户的架构设置权限。

(See herefor details)

（详情请看这里）

Two other commands you might find useful are ALTER USERand ALTER LOGIN.

您可能会发现有用的另外两个命令是ALTER USER和ALTER LOGIN。

回答by Ali asghar Fendereski

CREATE LOGIN AdminLOGIN WITH PASSWORD = 'pass'
GO


Use MyDatabase;
GO

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'AdminLOGIN')
BEGIN
    CREATE USER [AdminLOGIN] FOR LOGIN [AdminLOGIN]
    EXEC sp_addrolemember N'db_owner', N'AdminLOGIN'
    EXEC master..sp_addsrvrolemember @loginame = N'adminlogin', @rolename = N'sysadmin'
END;
GO

this full help you for network using:

这完全可以帮助您使用以下网络：

1- Right-click on SQL Server instance at root of Object Explorer, click on Properties
Select Security from the left pane.

2- Select the SQL Server and Windows Authentication mode radio button, and click OK.

3- Right-click on the SQL Server instance, select Restart (alternatively, open up Services and restart the SQL Server service).

4- Close sql server application and reopen it

5- open 'SQL Server Configuration Manager' and tcp enabled for network

6-Double-click the TCP/IP protocol, go to the IP Addresses tab and scroll down to the IPAll section.

7-Specify the 1433 in the TCP Port field (or another port if 1433 is used by another MSSQL Server) and press the OK 

8-Open in Sql Server: Security And Login  And Right Click on Login Name And Select Peroperties And Select Server Roles And 
  Checked The Sysadmin And Bulkadmin then Ok.
9-firewall: Open cmd as administrator and type: 
      netsh firewall set portopening protocol = TCP port = 1433 name = SQLPort mode = ENABLE scope = SUBNET profile = CURRENT

回答by Mohammad shahnawaz

This past week I installed Microsoft SQL Server 2014 Developer Edition on my dev box, and immediately ran into a problem I had never seen before.

上周我在我的开发箱上安装了 Microsoft SQL Server 2014 Developer Edition，并立即遇到了一个我以前从未见过的问题。

I've installed various versions of SQL Server countless times, and it is usually a painless procedure. Install the server, run the Management Console, it's that simple. However, after completing this installation, when I tried to log in to the server using SSMS, I got an error like the one below:

我无数次安装了各种版本的 SQL Server，这通常是一个轻松的过程。安装服务器，运行管理控制台，就是这么简单。但是，完成此安装后，当我尝试使用 SSMS 登录服务器时，出现如下错误：

SQL Server login error 18456 “Login failed for user… (Microsoft SQL Server, Error: 18456)” I'm used to seeing this error if I typed 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 set up with Windows Authentication only, and the user account was my own. I'm still not sure why it didn't add my user to the SYSADMIN role during setup; perhaps I missed a step and forgot to add it. At any rate, not all hope was lost.

SQL Server 登录错误 18456“用户登录失败……（Microsoft SQL Server，错误：18456）” 如果我在登录时输入错误的密码，我已经习惯了看到这个错误——但这仅当我使用混合模式时（ Windows 和 SQL 身份验证）。在这种情况下，服务器仅设置了 Windows 身份验证，用户帐户是我自己的。我仍然不确定为什么在安装过程中没有将我的用户添加到 SYSADMIN 角色；也许我错过了一步而忘记添加它。无论如何，并不是所有的希望都破灭了。

The way to fix this, if you cannot log on with any other account to SQL Server, is to add your network login through a command line interface. For this to work, you need to be an Administrator on Windows for the PC that you're logged onto.

如果您无法使用任何其他帐户登录 SQL Server，解决此问题的方法是通过命令行界面添加您的网络登录。为此，您需要是您登录的 PC 的 Windows 管理员。

Stop the MSSQL service. Open a Command Prompt using Run As Administrator. Change to the folder that holds the SQL Server EXE file; 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. While leaving this Command Prompt open, open another one, repeating steps 2 and 3. In the second Command Prompt window, run “SQLCMD –S Server_Name\Instance_Name” In this window, run the following lines, pressing Enter after each one: 1

停止 MSSQL 服务。使用以管理员身份运行打开命令提示符。切换到保存 SQL Server EXE 文件的文件夹；SQL Server 2014 的默认值是“C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn”。运行以下命令：“sqlservr.exe –m”。这将以单用户模式启动 SQL Server。在打开此命令提示符的同时，打开另一个命令提示符，重复步骤 2 和 3。在第二个命令提示符窗口中，运行“SQLCMD –S Server_Name\Instance_Name” 在此窗口中，运行以下行，在每行之后按 Enter： 1

CREATE LOGIN [domainName\loginName] FROM WINDOWS 2 GO 3 SP_ADDSRVROLEMEMBER 'LOGIN_NAME','SYSADMIN' 4 GO Use CTRL+C to end both processes in the Command Prompt windows; you will be prompted to press Y to end the SQL Server process.

CREATE LOGIN [domainName\loginName] FROM WINDOWS 2 GO 3 SP_ADDSRVROLEMEMBER 'LOGIN_NAME','SYSADMIN' 4 GO 在命令提示符窗口中使用 CTRL+C 结束两个进程；系统将提示您按 Y 结束 SQL Server 进程。

Restart the MSSQL service. That's it! You should now be able to log in using your network login.

重新启动 MSSQL 服务。就是这样！您现在应该可以使用您的网络登录名登录。