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
LVL 50
Dale FyeAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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'
0
 
chaauCommented:
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
0
 
Dale FyeAuthor 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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Anthony PerkinsCommented:
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.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Dale Fye, do you still need help with this question?
0
 
Dale FyeAuthor Commented:
vitor,

Yes.  decided not to go the active directory group route recommended above.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
 
Dale FyeAuthor 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.
0
 
Dale FyeAuthor Commented:
I'm not blowing you off, Vitor.  Just have not had the opportunity to test this.
0
 
Dale FyeAuthor Commented:
Thanks, Vitor.

Sorry it took so long to get back to this one.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.