我正在尝试创建一个存储过程来创建登录名和数据库用户? [英] I am trying to create a stored procedure to create a login and a database user?

问题描述

我正在使用 SQL Server 开发 C# 应用程序，并且想要调用存储过程来创建登录名和用户.

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.

对于 create 用户，它只是像这样的单行吗?

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]

我看不懂FOR 和FROM ...

我可以这样做吗:

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]

如何在 Google 上搜索以下来源:

How have Googled the following sources:

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

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

推荐答案

考虑使用动态 SQL 创建用户.例如，要使用名为 'DEV' + @login 的数据库用户创建名为 @login 的服务器登录:

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

在客户端构建 SQL 语句可能更容易.但即便如此，您也不能在 create login 语句中使用参数.所以要对 SQL 注入保持警惕.

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.

这篇关于我正在尝试创建一个存储过程来创建登录名和数据库用户?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！