December 13, 2007 at 3:51 am
Is there a way (using either T-SQL or C#) to specify User Mapping when creating a user programmatically?
I'm using the following code to create a user:
Use StarWatch
go
CREATE LOGIN [PCStarWatch] FROM WINDOWS
WITH DEFAULT_DATABASE=[StarWatch]
GO
... However, this is part of the installation of the application, and the login is actually a Windows domain GROUP and needs to be mapped to a user. This is required to be done via the code, and not manually using the SQL Server Management Studio.
Please ... any help would be vastly appreciated !!
December 14, 2007 at 9:15 am
I use this as part of our application security.
this proc can be executed once the login has been added to the server:
CREATE PROCEDURE [ADMIN].[s_addWindowsUserToDB]
@username varchar(50),
@login varchar(50),
@grpname varchar(50),
@adminuser varchar(50)
AS
-- first add the user to the database
DECLARE @sql varchar(200)
SET @sql = 'CREATE USER [' + @username + '] FOR LOGIN [' + @login + ']'
EXECUTE(@sql)
-- log the action
INSERT INTO ADMIN.t_UserManagementLogging(uml_AdminUser, uml_SubjectUser, uml_DBRole, uml_Action)
VALUES(@adminuser, @username, 'Database', 'Add')
-- then add the user to the default database role, if supplied
IF @grpname <> ''
BEGIN
SET @sql = 'EXEC sp_addrolemember ''' + @grpname + ''', ''' + @username + ''''
EXEC (@sql)
END
-- log the action
INSERT INTO ADMIN.t_UserManagementLogging(uml_AdminUser, uml_SubjectUser, uml_DBRole, uml_Action)
VALUES(@adminuser, @username, @grpname, 'Add')
the trick here is that these system stored procedures can be only run by database users with db_accessadmin (to add the user to the role) and db_securityadmin rights (to add the user to the db). I solved that by creating a dbuser (AppAdmin) with only those permissions and granting that user execute permissions on this and other procs written to handle the app security. From the applications perspective, the connection to the db to execute this procedure is made with the AppAdmin user's credentials. The @adminuser parameter sent into the proc is the name of the actual user of the application. This allows me to track who is adding/deleting/editing whom in the database.
December 19, 2007 at 4:35 am
Thanks, Sarah, absolutely magic, works fine !!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply