Problem with adding users to database

Question

Problem with adding users to database

Inno

SSC Enthusiast

Points: 170
More actions
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

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

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

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 1

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.

Inno SSC Enthusiast Points: 170 More actions · Answer 2

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

Inno SSC Enthusiast Points: 170 More actions · Answer 3

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

Inno SSC Enthusiast Points: 170 More actions · Answer 4

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

SpringTownDBA SSCertifiable Points: 6917 More actions · Answer 5

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.

SpringTownDBA SSCertifiable Points: 6917 More actions · Answer 6

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

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