blocks|key|1211767|text|您可以使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1211768|CREATE+LOGIN+<login+name>+WITH+PASSWORD+=+'<password>'+;+GO+|code-block|syntax|javascript|1211769|创建登录(有关更多详细信息，请参阅here+)。|offset|length|1211770|然后，您可能需要使用：|1211771|CREATE+USER+user_name+|1211772|要创建与特定数据库的登录名相关联的用户，还需要授予他们访问权限。|1211773|(详情请参见here+)|1211774|您还可以使用：|1211775|GRANT+permission++[+,...n+]+ON+SCHEMA+::+schema_name|1211776|为您为其分配用户的架构设置权限。|1211777|(详情请参见here+)|1211778|您可能会发现另外两个有用的命令是ALTER+USER和ALTER+LOGIN。|1211779|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms189751.aspx|1|http://technet.microsoft.com/en-us/library/ms173463.aspx|2|http://msdn.microsoft.com/en-us/library/ms187940.aspx|3|http://msdn.microsoft.com/en-us/library/ms176060.aspx|4|http://msdn.microsoft.com/en-us/library/ms189828.aspx^0|0|0|H|4|0|0|0|0|0|6|4|1|0|0|0|0|6|4|2|0|G|A|3|R|B|4|0^^$0|@$1|2|3|4|5|6|7|1I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|1J|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|1K|8|@]|9|@$I|1L|J|1M|1|1N]]|A|$]]|$1|K|3|L|5|6|7|1O|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|1P|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1R|8|@]|9|@$I|1S|J|1T|1|1U]]|A|$]]|$1|S|3|T|5|6|7|1V|8|@]|9|@]|A|$]]|$1|U|3|V|5|D|7|1W|8|@]|9|@]|A|$E|F]]|$1|W|3|X|5|6|7|1X|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1Y|8|@]|9|@$I|1Z|J|20|1|21]]|A|$]]|$1|10|3|11|5|6|7|22|8|@]|9|@$I|23|J|24|1|25]|$I|26|J|27|1|28]]|A|$]]|$1|12|3|-4|5|6|7|29|8|@]|9|@]|A|$]]]|13|$14|$5|15|16|17|A|$18|19]]|1A|$5|15|16|17|A|$18|1B]]|1C|$5|15|16|17|A|$18|1D]]|1E|$5|15|16|17|A|$18|1F]]|1G|$5|15|16|17|A|$18|1H]]]]

You can use:

<pre><code>CREATE LOGIN &lt;login name&gt; WITH PASSWORD = '&lt;password&gt;' ; GO 
</code></pre>

To create the login (See <a href="http://msdn.microsoft.com/en-us/library/ms189751.aspx" rel="nofollow noreferrer">here</a> for more details).

Then you may need to use:

<pre><code>CREATE USER user_name 
</code></pre>

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

(See <a href="http://technet.microsoft.com/en-us/library/ms173463.aspx" rel="nofollow noreferrer">here</a> for details) 

You can also use:

<pre><code>GRANT permission [ ,...n ] ON SCHEMA :: schema_name
</code></pre>

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

(See <a href="http://msdn.microsoft.com/en-us/library/ms187940.aspx" rel="nofollow noreferrer">here</a> for details)

Two other commands you might find useful are <a href="http://msdn.microsoft.com/en-us/library/ms176060.aspx" rel="nofollow noreferrer">ALTER USER</a> and <a href="http://msdn.microsoft.com/en-us/library/ms189828.aspx" rel="nofollow noreferrer">ALTER LOGIN</a>.

blocks|key|1211847|text|如果要创建通用脚本，可以使用Execute语句并将其替换为您的用户名和数据库名|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1211848|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)|code-block|syntax|javascript|1211849|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>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)
</code></pre>

I want to write a script to create a <code>admin</code> user ( with <code>abcd</code> password ) in SQL Server Express.
Also I want to assign this user <code>admin</code> full rights.

SQL Server Script to create a new user

SQL Server

数据库

我想要编写一个脚本，以便在SQL Server Express中创建一个admin用户(使用abcd密码)。另外，我想给这个用户分配admin完全权限。

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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