We help IT Professionals succeed at work.

Add user login and assign database and role permissions via T-SQL stored procedure

I'd like to write a stored procedure which will accept a userid as a parameter and will then add that user to server and assign the user read and write permissions to all of the databases on the server.  I will eventually modify this procedure to specify specific permissions but for now, would like to simply use Read and Write permissions.

I am using Windows Authentication
Comment
Watch Question

Top Expert 2013

Commented:
If you are using Windows Authentication I can propose a better way. You can create an Active Directory user group and assign all proper SQL Server rights and roles for this group. Then all you have to do is to add the AD user to this group. You can create multiple AD groups
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
chaau,

Assuming I can get my client to create those AD groups, how would I write the VB script to add/remove a particular user to/from an AD group?

Dale
Top Expert 2012

Commented:
how would I write the VB script to add/remove a particular user to/from an AD group?
How were you planning on adding/removing users in AD in the first place? If you use Windows Groups the only additional step is to add that user to the Windows Group.  That's it.  It does not get more complicated than that.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Dale Fye, do you still need help with this question?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
vitor,

Yes.  decided not to go the active directory group route recommended above.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
So, you need to create the same SP in all SQL Server instances and maybe in the master database, right?
The SP will create the Login and the respective user in all databases of the instance? Or the Login need to exist in the SQL Server instance?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Vitor,

The login would need to exist in the SQL Server instance, but I'm only working with one instance at the moment.

What I would like to do is pass in the users Windows ID and an optional database name.  

The SP would test to see whether it already exists and if not, would add it to the SQL Server instance with Windows Authentication.

Then, if I pass in a database name, it would check to make sure that user doesn't already have access to that database.  If it doesn't, it would grant the user read and write permissions to that database.  If I don't pass in the database name, it would grant read write permissions to all of the databases on that server instance.

I'm not sure why I would need to grant them permissions to the Master or Tempdb, but I guess if there were any stored procedures that they might call that used temp tables, that they would also need access to Tempdb.
IT Engineer
Distinguished Expert 2017
Commented:
This SP should do it:
CREATE PROCEDURE CreateUser @Login VARCHAR(128), @Database VARCHAR(128) = NULL 
AS

	DECLARE @SQLCommand NVARCHAR(MAX)
	
	IF @Login IS NULL 
		BEGIN
			PRINT 'Invalid Login Name'
			RETURN
		END
		
	IF @Database IS NULL
		BEGIN
			SET @SQLCommand = N'USE ? CREATE USER ' + @Login + ' FOR LOGIN ' + @Login 
						+ '; EXEC sp_addrolemember ''db_datareader'', ''' + REPLACE(REPLACE(@Login,'[',''),']','') 
						+ '''; EXEC sp_addrolemember ''db_datawriter'', ''' + REPLACE(REPLACE(@Login,'[',''),']','') + ''''
			EXEC sp_MSforeachdb @SQLCommand
		END
	ELSE
		BEGIN
			SET @SQLCommand = N'USE ' + @Database + ' CREATE USER ' + @Login + ' FOR LOGIN ' + @Login
						+ '; EXEC sp_addrolemember ''db_datareader'', ''' + REPLACE(REPLACE(@Login,'[',''),']','') 
						+ '''; EXEC sp_addrolemember ''db_datawriter'', ''' + REPLACE(REPLACE(@Login,'[',''),']','') + ''''
			EXEC sp_executesql @SQLCommand
		END

	RETURN

Open in new window


NOTE: For calling for a Windows Domain user don't forget to use brackets:  exec CreateUser '[DomainName\UserName]', 'DBName'
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
I'm not blowing you off, Vitor.  Just have not had the opportunity to test this.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Thanks, Vitor.

Sorry it took so long to get back to this one.