Dynamically creating users

[DeanWilliams]

Hi all,

Is there a way to dynamically create SQL users?

I am working on an ASP.NET web application and am using a modified version of the stored procedure which adds users to the aspnet roles database.

You can pass in comma separated lists of domain usernames and roles as parameters and it adds all the users to all the aspnet roles specified.

I would like to extend it to allow it to create the SQL logins and users, and then add them to the correct SQL roles.

So I was wondering if I can use an equivalent to: CREATE USER domain\username FOR LOGIN domain\username WITH DEFAULT_SCHEMA=[dbo], but using string variables for the names, as well as statements to add them to SQL roles, using string variables for the role names.

Hope this makes sense,
Thanks in advance.
Dean.

 

[DeanWilliams]

I have found the answer:


EXEC ('CREATE LOGIN [domain\username] FROM WINDOWS WITH DEFAULT_DATABASE=[databasename];')

EXEC ('CREATE USER [domain\username] FOR LOGIN [domain\username] WITH DEFAULT_SCHEMA=[dbo];')

EXEC sp_addrolemember N'sqlrolename', N'domain\username'