Adding db users to multiple db

Question

Adding db users to multiple db

Viewing 9 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

RTSConsult SSC Enthusiast Points: 108 More actions · Answer 1

thanks that will be great. I am getting the below which must be a syntax i have wrong. When i put in the servename\instance or local or default. Is there something i am missing here?

Thanks again.

Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a connection to SQL Server. Th

e server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configure

d to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

At C:\addsqlusers.ps1:35 char:18

+ Invoke-Sqlcmd <<<< -SuppressProviderContextWarning -Query $create_login_cmd -ServerInstance $instance -OutputSql

Errors $true -Verbose

+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException

+ FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 2

Are you running this on the same server where the SQL instance is installed?

Use the same server info you would plug into SSMS.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

RTSConsult SSC Enthusiast Points: 108 More actions · Answer 3

ah nevermind i just figured it out. i can just put in the servername only. apparently local does not work for this...

thanks again great help here as always!

RTSConsult SSC Enthusiast Points: 108 More actions · Answer 4

yes i did for testing purposes i ran it from the test server which is also the sql server.

thanks

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 5

Excellent! HTH 🙂

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

RTSConsult SSC Enthusiast Points: 108 More actions · Answer 6

Yes Sir! 🙂

so hate to do this to you but a couple more questions arose once i ran this and it worked as expected.

1. Can i place a string as part of the variable for the db name so that it only picks from databases with similar names ie 'lasvegas'db as opposed to a 'denver'db?

2. Can I insert a prompt for the user name as oppposed to a text file. Leaves room for user error but may be a requirement.

Thanks in advance again.

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 7

RTSConsult (9/20/2011)
Yes Sir! 🙂
so hate to do this to you but a couple more questions arose once i ran this and it worked as expected.
1. Can i place a string as part of the variable for the db name so that it only picks from databases with similar names ie 'lasvegas'db as opposed to a 'denver'db?

If you go back to the initial version of the posted script you can add a filter to the WHERE clause when selecting from sys.databases to get that behavior.

2. Can I insert a prompt for the user name as oppposed to a text file. Leaves room for user error but may be a requirement.

If you save the following to a .ps1 file...

param([string]$instance)

if ($instance -eq "") {

# Get input from interactive user

$instance = Read-Host "Enter DB instance"

}

"Instance: $instance"

...you can run it from the command line with or without input parameters. When none are provided it will prompt the user for the parameter value:

PS C:\@\SQL_Scripts\DBA\SQL Server\PowerShell> .\stdin.ps1 -instance SERVER\INSTANCE

Instance: SERVER\INSTANCE

PS C:\@\SQL_Scripts\DBA\SQL Server\PowerShell> .\stdin.ps1

Enter DB instance: SERVER\INSTANCE

Instance: SERVER\INSTANCE

PS C:\@\SQL_Scripts\DBA\SQL Server\PowerShell>

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

RTSConsult SSC Enthusiast Points: 108 More actions · Answer 8

hmm ok so thr param is after the where clause. and the string value would be my repeating value for the name of the db correct?

as for the user name it appears from the xtra bits that you are prompting for the db instance as oppposed to the user name?? Am i confused or reading that right?

Apologies but i am a newbie to the scripting tools.

thanks again

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 9

RTSConsult (9/20/2011)
hmm ok so thr param is after the where clause. and the string value would be my repeating value for the name of the db correct?
as for the user name it appears from the xtra bits that you are prompting for the db instance as oppposed to the user name?? Am i confused or reading that right?
Apologies but i am a newbie to the scripting tools.
thanks again

OK, putting it all together...see if this one works out for you:

param([string]$windows_user)

if ($windows_user -eq "") {

# Get input from interactive user

$windows_user = Read-Host "Enter User"

}

# load SQL Server snap-ins

Add-PSSnapin *SQL*

############################################################################################

# set instance name, e.g. SERVERNAME for default instance, or SERVERNAME\INSTANCENAME for named instance

$instance = "SERVERNAME\INSTANCENAME"

# set database role to add each user to

$db_role_name = 'db_owner'

# set string to use to look for databases

$db_like = "%cashvegas%"

############################################################################################

$create_login_cmd = "

-- create server login

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'$windows_user')

CREATE LOGIN [$windows_user] FROM WINDOWS WITH DEFAULT_DATABASE=[tempdb], DEFAULT_LANGUAGE=[us_english] ;

DECLARE @cmd NVARCHAR(MAX) ;

SET @cmd = N'' ;

-- create user in databases and add to database role

SELECT @cmd = @cmd + 'USE ' + name + ' ;

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ''$windows_user'')

CREATE USER [$windows_user] FOR LOGIN [$windows_user] ;

EXEC sys.sp_addrolemember N''$db_role_name'', N''$windows_user'' ;

'

FROM sys.databases

WHERE database_id > 4 -- not a system database

AND state_desc = 'ONLINE'

AND is_read_only = 0

AND name LIKE '$db_like' ;

PRINT @cmd ;

EXEC(@cmd) ;"

Invoke-Sqlcmd -SuppressProviderContextWarning -Query $create_login_cmd -ServerInstance $instance -OutputSqlErrors $true -Verbose

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato