I am trying to create a stored procedure to create

问题:

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.

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

回答1:

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.

回答2:

{ 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

回答3:

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

回答4:

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