SQL Server Script to create a new user

Question

I want to write a script to create a admin user   with abcd password   in SQL Server Express  Also I want to assign this user admin full rights

User · Answer

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.

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 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.

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.

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

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.

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

User · Answer

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

User · Answer

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

User · Answer

You can use   CREATE LOGIN  lt login name gt  WITH PASSWORD     lt password gt     GO    To create the login  See here for 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 here for 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 here for details   Two other commands you might find useful are ALTER USER and ALTER LOGIN

User · Answer

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  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

User · Answer

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 always useful to include this information in your question

[sql] SQL Server Script to create a new user

Examples related to sql

Examples related to sql-server

Examples related to database