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
I am using Windows Authentication
chaau
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
ASKER
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
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
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.
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.
Dale Fye, do you still need help with this question?
ASKER
vitor,
Yes. decided not to go the active directory group route recommended above.
Yes. decided not to go the active directory group route recommended above.
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?
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?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm not blowing you off, Vitor. Just have not had the opportunity to test this.
ASKER
Thanks, Vitor.
Sorry it took so long to get back to this one.
Sorry it took so long to get back to this one.