March 6, 2013 at 7:43 am
I am attempting to create T-SQL that I can re-use on different db’s, users, domains, roles, etc…
The trouble I’m having is when trying to change database names dynamically. I need the ability to hit multiple db’s in one shot. My goal for starters is to only change the username parameter for each user/executio and execute the rest.
I have attached the code. Any suggestions would be helpful - there must be a better way.
This is what’s failing after adding the +(@DB) variable:
set @USER = + QUOTENAME(@DB) + 'CREATE USER ' + QUOTENAME(@DN + @UN) + ' FOR LOGIN ' + quotename(@DN + @UN) + ' WITH DEFAULT_SCHEMA=[' + @DN + @UN + ']' --db1
March 6, 2013 at 7:51 am
QUOTENAME (@DB) is resulting in [USE MyDB] - needs to be USE [MyDB]
March 6, 2013 at 8:16 am
Gazareth (3/6/2013)
QUOTENAME (@DB) is resulting in [USE MyDB] - needs to be USE [MyDB]
Good catch! Still open to a better overall approach for this.
March 6, 2013 at 8:32 am
Approach is fine. The other way to do it if possible would be to give role membership(s) to AD group(s), and control user membership in AD.
That way you won't need to create a new user everywhere all the time.
Or look into using powershell - might give you a slightly tidier script that can easily be pointed at multiple servers.
March 6, 2013 at 10:07 am
Gazareth (3/6/2013)
The other way to do it if possible would be to give role membership(s) to AD group(s), and control user membership in AD.That way you won't need to create a new user everywhere all the time.
AD groups would be ideal.
March 7, 2013 at 3:08 am
It's a good way to go about it. A fair bit of work to set up, but much easier after that!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply