Post reply

Problem with adding users to database

  • August 10, 2012 at 12:57 am

    #259827

    Hey guys.

    First of all I'm sorry if I can't really explain my issues, but I'm a total noob in sql.

    I have created a procedure to add users to my database, which I call in C#. The only problem is the organisation. It won't let me add new users only if the users are directly created on the server.

    To make it more clear, i will add some screenshots.

    And here is my procedure:

    USE [Abosystem4_Archiv_test]

    GO

    /****** Object: StoredProcedure [dbo].[CreateUser] Script Date: 08/10/2012 08:56:46 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    -- =============================================

    -- Author: <Author,,Name>

    -- Create date: <Create Date,,>

    -- Description: <Description,,>

    -- =============================================

    ALTER PROCEDURE [dbo].[CreateUser]

    @username nvarchar(100),

    @password nvarchar(100)

    -- Add the parameters for the stored procedure here

    WITH EXECUTE AS 'sv'

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- Insert statements for procedure here

    EXEC('CREATE LOGIN ' + @username + ' WITH PASSWORD = ' + '''' + @password + '''' +' ,CHECK_POLICY=OFF ')

    print('CREATE LOGIN ' + @username + ' WITH PASSWORD = ' + '''' + @password + '''' +' ,CHECK_POLICY=OFF ')

    EXEC ('CREATE USER '+ @username + ' for login '+ @username)

    print('CREATE USER '+ @username + ' for login '+ @username)

    EXEC sp_addrolemember 'dds_arcivebase_user', @username

    END

    So creating the user on the local database is functioning if the users exists on the server. But if there's no user created on the server, then the whole procedure is not functioning - it says that I don't have the rights for creating a user, and the permissions are granted.

    Please help

  • August 10, 2012 at 1:31 am

    #1524235

    Database users are mapped to server logins (and vice versa). I'm afraid you will need a server login to associate a new database user with. The server level permissions can then be granted to the login and the DB level permissions to the user. Ideally of course you will be using database roles, so users are members of roles which are associated with logins.

    If this seems confusing please read the blog post below (not mine) which will explain about security in SQL.

    http://blogs.msdn.com/b/lcris/archive/2007/03/23/basic-sql-server-security-concepts-logins-users-and-principals.aspx

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • August 10, 2012 at 1:39 am

    #1524237

    Well I have approles and everything else....but the mapping is my problem...I just can't create the users. My programm works fine but, the SQL part...it's mind bobbling. And I'm a total noob. COuld u please explain me how to add the users to the master database?

    I undertand the differences, i already made approles and so on, and I am also using them. Everything works absolutely great, but I can't add the users in the master...and that's my problem. I am not worrying about rights, that problem I've cleared with approles..

  • August 10, 2012 at 2:40 am

    #1524255

    Done that....removed EXECUTE AS OWNER...and now it works..

  • August 10, 2012 at 3:20 am

    #1524275

    Ohh and then again it's not working. I can now create users but my programm crashes if I delte the execute as command

  • August 10, 2012 at 8:49 am

    #1524487

    Ok, a couple of things:

    1. Your code is not robust. You are not properly escaping your input. You MUST use QUOTENAME() to sanitize the input when doing string concatentation.

    EXEC('CREATE LOGIN ' + QUOTENAME(@username) + ' WITH PASSWORD = ' + QUOTENAME(@password,'''') +' ,CHECK_POLICY=OFF ')

    If you don't then at a minimum your script will fail if username has a space or the password has a single quote in it. The worst case is a security vulnerability which would allow someone to execute arbitrary sql code in your stored procedure, aka, SQL INJECTION (what happens if @password = "'; DROP TABLE foobar; -- " ? ) And since your code is creating a LOGIN, it has to have elevated permissions.

    2. People connect to SQL server via LOGINS (at the SERVER level). Those LOGINS are mapped to USERS in various DATABASES. If you want the stored procedure to be able to create LOGINS and USERS, then either the person running the stored procedure needs to have permissions at the SERVER to create LOGINS, -or- you must use "EXECUTE AS" and specify a user who has that permission.

    Although, honestly, I've never tried to delegate the creation of server level objects using 'EXECUTE AS' it may not be possible. I really need to re-watch those technet SQL 2008 MCM videos on security. I'm pretty sure that it was covered in there.

    My initial thought is that execute as db user needs to be mapped to a login with with appropriate server permissions. Additionally, the DB may need to be flagged as trustworthy.

    3. creating DB USERS isn't a big deal with EXECUTE AS. the execute as user just needs the database permission.

  • August 10, 2012 at 11:03 am

    #1524563

    A better option is to use a certificate signed stored procedure.

    http://msdn.microsoft.com/en-us/library/bb283630(v=sql.105).aspx

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply