I am trying to create a stored procedure to create a login and a database user?

I am developing a C# application with SQL Server, and would like to call a stored procedure to create a login and a user.

I am looking for the simplest way to do this, just default stuff but have username and password.

  • SQL Server: Are temp tables or unions better?
  • Cannot find the user '', because it does not exist or you do not have permission
  • Best way to allow the User to define a Table’s Order?
  • Data flow task fails at source because of datetime conversion? SSIS 2008
  • Getting the primary key of an newly inserted row in SQL Server 2008
  • T-SQL Date Check between Parent and Child Tables using after triggers
  • The create next to login is underlined in red?

    But I think my login is fine? Please tell me if I’m missing something.

    And with the create user, is it just a one liner like that?

    CREATE PROCEDURE CreateLoginAndUser 
           @UserName
    
       CREATE LOGIN @UserName 
       WITH PASSWORD = 'password', 
            DEFAULT_DATABASE = [TestAudit],
            DEFAULT_LANGUAGE = [British],
            CHECK_EXPIRATION=OFF,
            CHECK_POLICY=ON;
    
       CREATE USER 'DEV' + @UserName 
           [{ FOR | FROM } LOGIN @UserName] [WITH DEFAULT_SCHEMA = schema]
    

    I don’t understand FOR and FROM

    Could I do something like this:

    CREATE PROCEDURE CreateLoginAndUser
         @UserName
    
        CREATE LOGIN @UserName  
        WITH PASSWORD = 'password', 
             DEFAULT_SCHEMA = schema    
    
        CREATE USER 'DEV'+@UserName 
           [{ FOR | FROM } LOGIN @UserName] [WITH DEFAULT_SCHEMA = schema]
    

    How have Googled the following sources:

    http://ss64.com/sql/login_c.html

    http://ss64.com/sql/user_c.html

    4 Solutions collect form web for “I am trying to create a stored procedure to create a login and a database user?”

    Consider using dymanic SQL to create a user. For example, to create a server login called @login with a database user called 'DEV' + @login:

    create procedure dbo.CreateLoginAndUser(
            @login varchar(100),
            @password varchar(100),
            @db varchar(100))
    as
    declare @safe_login varchar(200)
    declare @safe_password varchar(200)
    declare @safe_db varchar(200)
    set @safe_login = replace(@login,'''', '''''')
    set @safe_password = replace(@password,'''', '''''')
    set @safe_db = replace(@db,'''', '''''')
    
    declare @sql nvarchar(max)
    set @sql = 'use ' + @safe_db + ';' +
               'create login ' + @safe_login + 
                   ' with password = ''' + @safe_password + '''; ' +
               'create user DEV' + @safe_login + ' from login ' + @safe_login + ';'
    exec (@sql)
    go
    

    It might be easier to construct the SQL statement client-side. But even then, you couldn’t use parameters with the create login statement. So be on your guard about SQL Injection.

    { FOR | FROM } means you can use for or from.

    | (vertical bar) = Separates syntax items enclosed in brackets or braces. You can use only one of the items.

    EDIT:

    I should have added that they are synonyms, you can use any of them, the result is the same

    You should consider user rights. To create sql server login and database user, you have to have high privileges on SQL Server instance. So either you have to grant user rights to do the action (and that mean- server level and database lever permissions) or you have to use other mechanism not to make your server vulnerable.

    One of the option’s would be using certificates and signet stored procedures to do things “nicely” and not allow creation of logins/users directly.

    Also, the one who is able to use trace, will be able to see passwords (if you are not wrapping that in procedure, password is not shown in trace).

    here is an example on how to create a login for the server-wide, and a user for your db
    adapt it to a procedure

    USE [master]
    GO
    CREATE LOGIN [usr] WITH PASSWORD=N'pass', DEFAULT_DATABASE=[yourDb], DEFAULT_LANGUAGE=[English], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO
    
    USE [yourDb]
    GO
    CREATE USER [usr] FOR LOGIN ['usr]
    GO
    

    you only have to receive usr and password as a parameter and use it

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.