Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Add login, user, roles in mult db's dynamically Expand / Collapse
Author
Message
Posted Wednesday, March 6, 2013 7:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:45 PM
Points: 161, Visits: 665
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


  Post Attachments 
createUserSample.txt (9 views, 2.50 KB)
Post #1427410
Posted Wednesday, March 6, 2013 7:51 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 11:20 AM
Points: 1,945, Visits: 3,204
QUOTENAME (@DB) is resulting in [USE MyDB] - needs to be USE [MyDB]
Post #1427417
Posted Wednesday, March 6, 2013 8:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:45 PM
Points: 161, Visits: 665
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.
Post #1427445
Posted Wednesday, March 6, 2013 8:32 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 11:20 AM
Points: 1,945, Visits: 3,204
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.
Post #1427456
Posted Wednesday, March 6, 2013 10:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:45 PM
Points: 161, Visits: 665
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.
Post #1427512
Posted Thursday, March 7, 2013 3:08 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 11:20 AM
Points: 1,945, Visits: 3,204
It's a good way to go about it. A fair bit of work to set up, but much easier after that!
Post #1427863
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse