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 52
Dale FyeOwner, Developing Solutions LLCAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 FyeOwner, Developing Solutions LLCAuthor 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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Vitor MontalvãoMSSQL Senior EngineerCommented:
Dale Fye, do you still need help with this question?
0
Dale FyeOwner, Developing Solutions LLCAuthor 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 FyeOwner, Developing Solutions LLCAuthor 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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCAuthor Commented:
I'm not blowing you off, Vitor.  Just have not had the opportunity to test this.
0
Dale FyeOwner, Developing Solutions LLCAuthor Commented:
Thanks, Vitor.

Sorry it took so long to get back to this one.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.