如何在MS-SQL Server 2005中创建新用户 [英] How can I create a new user in MS-Sql Server 2005

问题描述

USE [master]
GO
CREATE LOGIN [shi] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
EXEC master..sp_addsrvrolemember @loginame = N'shi', @rolename = N'sysadmin'
GO
USE [SMS2]
GO
CREATE USER [shi] FOR LOGIN [shi]
GO
USE [SMS2]
GO
ALTER USER [shi] WITH DEFAULT_SCHEMA=[dbo]
GO

出现异常:

Getting Exception:

Msg 15407, Level 16, State 1, Line 1
'shi' is not a valid Windows NT name. Give the complete name: <domain\username>.
Msg 15007, Level 16, State 1, Procedure sp_addsrvrolemember, Line 68
'shi' is not a valid login or you do not have permission.
Msg 15007, Level 16, State 1, Line 1
'shi' is not a valid login or you do not have permission.
Msg 15151, Level 16, State 1, Line 1
Cannot alter the user 'shi', because it does not exist or you do not have permission.

推荐答案

您必须首先以用户身份创建它，然后为该用户设置正确的权限.

1.您必须确保为数据库配置了两个User auth and SQL auth.如果使用 Management Studio :在服务器上单击鼠标右键，选择安全性" ，确保服务器身份验证为""SQL Server和Windows身份验证模式" ;

2.在Security-logins中，右键单击并选择新登录" ;，选择 SQL身份验证，使用您喜欢的username and password.

You''ll have to create it first as a user, and then set up the correct permissions for the user.

1. You''ll have to ensure that your DB is configured with both User auth and SQL auth. If using the Management Studio: right-click on the Server, select "Security" ensure that server authentication is "SQL Server and Windows Authentication mode";

2. In Security-logins, right click and select "New Login";, select SQL Authentication, use the username and password you like.

USE [master]
GO
CREATE LOGIN [ test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[MY_DATABASE], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO 

3.在所需的数据库上，在安全性中的用户中，选择新用户.选择一个用户名，并附加刚创建的登录名，然后选择要应用于该用户的角色(即db_datareader, db_datawriter):

3. On the DB you want, in security, users, select new User. Select a username, and attach the login name you''ve just created, and select the roles you want to apply to this user (i.e. db_datareader, db_datawriter):

USE [MY_DATABASE]
GO
CREATE USER [myDefaultUser] FOR LOGIN [test]
GO
USE [MY_DATABASE]
GO
EXEC sp_addrolemember N'db_datareader', N'myDefaultUser'
GO
USE [MY_DATABASE]
GO
EXEC sp_addrolemember N'db_datawriter', N'myDefaultUser'
GO

这就对了.现在，您可以使用此密码创建连接字符串.
参考: stackoverflow.com

That is it. Now you can create your connection string using this password.
Ref:stackoverflow.com

您好，

看看下面的链接是否对您有帮助:

http://sqlserverplanet.com/security/add-users-to-database-script [ ^ ]

http://msdn.microsoft.com/en-us/library/ms173463.aspx [ ^ ]

Hello,

See if below link helps you:

http://sqlserverplanet.com/security/add-users-to-database-script[^]

http://msdn.microsoft.com/en-us/library/ms173463.aspx[^]

这篇关于如何在MS-SQL Server 2005中创建新用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！