SQL Server Script to Create a New User

SQL Server Script to create a new user

Based on your question, I think that you may be a bit confused about the difference between a User and a Login. A Login is 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 User is a Login with access to a specific database.

Creating a Login is easy and must (obviously) be done before creating a User account for the login in a specific database:

CREATE LOGIN NewAdminName WITH PASSWORD = 'ABCD'
GO

Here 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 [NewAdminName] FOR LOGIN [NewAdminName]
    EXEC sp_addrolemember N'db_owner', N'NewAdminName'
END;
GO

Now, Logins are 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).

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 here for more information.

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.

Creating User and Password in SQL Server

Documentation: CREATE USER

Here is one way to create a LOGIN and USER (these are two different things):

USE [master]
GO
CREATE LOGIN [newbiee] WITH PASSWORD=N'asdfl@##@$kljaio234234bb' MUST_CHANGE
     , DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO

USE [test]
GO
CREATE USER [newbiee] FOR LOGIN [newbiee] WITH DEFAULT_SCHEMA=[dbo]
GO

How to add a user in an SQL Server database with the same rights as in another one on the same server?

Yes you can do it.

1. use the below script

USE [RCE_Finance_BI]
GO

CREATE USER [JeromeRCE] FOR LOGIN [JeromeRCE] WITH DEFAULT_SCHEMA=[dbo]
GO

Or you can create the script by below steps

1. Right click the user
2. Select 'Script User as'
3. Select 'Create To'
4. Select 'New Query Editor Window'
5. Change the database to you target database in the script.

how to Create SQL Server Database with User?

Assuming that you are using windows auth with a login 'domain\user' that has already been created.

--create the database
CREATE DATABASE NewDB

--create the user from the login
Use NewDB
CREATE USER [domain\user] FOR LOGIN [domain\user]

--To give user SELECT/UPDATE/INSERT/DELETE on all tables
EXEC sp_addrolemember 'db_datareader', 'domain\user'
EXEC sp_addrolemember 'db_datawriter', 'domain\user'

Alternatively to give the user admin over the database, replace the last two lines with.

--To give admin permissions
EXEC sp_addrolemember 'db_owner', 'domain\user'

CREATE DATABASE also has many options which you might need that can be found on BOL.

http://msdn.microsoft.com/en-us/library/ms176061.aspx

If you need to create a login also then you will need the following before creating the USER on your database.

--Using SQL Auth
CREATE LOGIN loginname WITH PASSWORD = 'passw0rd';

--Windows Auth
CREATE LOGIN domain\user FROM WINDOWS; 

SQL Server - Scripting CREATE USER WITHOUT LOGIN

Users without login were added to replace application roles.

Loginless users are usefull for impersonation, in order to gain necessary permissions. They allow users to authenticate to the instance with their own credentials, therefore making SQL Server able to audit activity to their login, while impersonating the loginless user on the database context.

Simple impersonation example:

SELECT SUSER_NAME(), USER_NAME();
GO
CREATE USER loginless_user_4test
    WITHOUT LOGIN
GO
EXECUTE AS USER = 'loginless_user_4test'
GO
SELECT SUSER_NAME(), USER_NAME();
GO
REVERT  --as long as you haven't issued "EXECUTE AS ... WITH NO REVERT", you can go back to previous context
GO

How do I create a new user in a SQL Azure database?

Check out this link for all of the information : https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure-database/

First you need to create a login for SQL Azure, its syntax is as follows:

CREATE LOGIN username WITH password='password';

This command needs to run in master db. Only afterwards can you run commands to create a user in the database. The way SQL Azure or SQL Server works is that there is a login created first at the server level and then it is mapped to a user in every database.

HTH

In SQL Server, how to add new login, new Users with parameters?

In T-SQL, double quotes (") are not string delimiters, they're used as identifiers (though most prefer [square brackets]). For string delimiters you need to use single quotes ('):

SET @NewUser = '[domain\first.last]';

But if you expect to be able to use that later, e.g.

DROP LOGIN @NewUser;

...many of those commands don't accept parameters like that. You'll need to construct them using dynamic SQL:

DECLARE @newUser nvarchar(255) = N'domain\first.last';
DECLARE @sql nvarchar(max) = N'DROP LOGIN $l$;';
SET @sql = REPLACE(@sql, N'$l$', QUOTENAME(@newUser));
PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Explain this SQL Script to create user and assign role

1: [Domain\Username] is for the windows Active Directory-user for that domain.

Example: Say you have an AD/Domain/Computer called StackOverFlow, that would be the Domain, and then your username is Awan, that makes it StackOverFlow\Awan

2: Correct, this is the username you want you just insert your username there (within the brackets)

Example [Awan]

3: sp_addrolemember is a function that adds the user with a specific role. (dbo and such)

4: N'DatabaseRole' is a numeric text where you write in what role your user is supposed to have. An example is db_owner, which makes the user able to create tables, delete tables, alter tables, and so on.

5: Exactly, you just put for example N'Awan' instead of N'Username'

Otherwise you can create a user from SSMS ( http://msdn.microsoft.com/en-us/library/aa337562.aspx )

---

Related Topics