September 19, 2011 at 12:32 pm
Hi folks,
I am looking for a script that can help with adding users to multiple db and assigning a db role at the same time. I would like to pull from a list of users in a txt or csv or even have a input dialogue. So for each user I plan on adding in the future it adds them to the db and gives the a preassigned role.
Any help would be appreciated.
/****** Object: Login [domain\user] Script Date: 09/19/2011 11:29:34 ******/
CREATE LOGIN [domain\user] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
USE [database]
GO
/****** Object: User [domain\user] Script Date: 09/19/2011 11:28:38 ******/
GO
CREATE USER [domain\user] FOR LOGIN [domain\user] WITH DEFAULT_SCHEMA=[dbo]
GO
September 19, 2011 at 12:50 pm
use the metadata to help generate teh required commands;
i think something like this can help:
SELECT
'USE '
+ name
+ '; IF NOT EXISTS (SELECT * FROM '
+ name
+ '.sys.database_principals WHERE name = ''bob'') CREATE USER [bob] FOR LOGIN [bob] ;'
+ 'EXEC sp_addrolemember N''WhateverDEVAdmins', N''bob'' '
from master.sys.databases
where database_id > 4
Lowell
September 19, 2011 at 2:18 pm
ok that may work. So do i still put in each db name or is it going through all db not under id 4? also for the user name by usingthe domain\user will that work as well?
September 19, 2011 at 2:24 pm
rob.sellinger 92152 (9/19/2011)
ok that may work. So do i still put in each db name or is it going through all db not under id 4? also for the user name by usingthe domain\user will that work as well?
the database_id's of 1 thru 4 are master,tempdb,model and msdb, and you would not add users to those databases typically.
just replace my user 'bob' with Mydomain'MyUser and test the code...all it does is generate teh statemetns for you, so you can test them one at a time.
just a timesaver to generate the commands from the metadata in system tables.
Lowell
September 19, 2011 at 2:52 pm
So i get an incorrect syntax on line 7 concerning the user name? Does not matter if i try to use a sql or domain user. there was also an error related to an unclosed quotation before the last 2 lines.
Any ideas?
September 19, 2011 at 3:14 pm
rob.sellinger 92152 (9/19/2011)
So i get an incorrect syntax on line 7 concerning the user name? Does not matter if i try to use a sql or domain user. there was also an error related to an unclosed quotation before the last 2 lines.Any ideas?
syntax...missing a single quote after the name of the role we want to add:
SELECT
'USE '
+ name
+ '; IF NOT EXISTS (SELECT * FROM '
+ name
+ '.sys.database_principals WHERE name = ''mydomain\domainGroup'') CREATE USER [mydomain\domainGroup] FOR LOGIN [mydomain\domainGroup] ;'
+ 'EXEC sp_addrolemember N''WhateverDEVAdmins'', N''mydomain\domainGroup'' '
from master.sys.databases
where database_id > 4
Lowell
September 19, 2011 at 3:30 pm
ah missed that...so on lines 3 and 5 the name as in your script it gives me an error invalid column name however this should be the name of the db correct?
September 19, 2011 at 3:33 pm
ah nevermind i see that is a variable of sorts. I have it now....thanks for the hand.
September 19, 2011 at 3:39 pm
I am still looking for a way to grab the user accounts from a list as well. Ultimately i would like to run this script to generate the new script as well as create the sql login account. Do you havea quick and dirty way to pull from a predefined list?
September 19, 2011 at 4:39 pm
rob.sellinger 92152 (9/19/2011)
I am still looking for a way to grab the user accounts from a list as well. Ultimately i would like to run this script to generate the new script as well as create the sql login account. Do you havea quick and dirty way to pull from a predefined list?
if all your uses are part of a domain group, you'd add the group instead of individual logins. it might see wierd, but for SQL, when you add something like [MyDomain\Authenticated Users] as a user, you add all domain users in that group.
anyway, bu simply cross joining to server_principals, i think this generates everyone or every group with a current login
SELECT
'USE '
+ dbz.name
+ '; IF NOT EXISTS (SELECT * FROM '
+ dbz.name
+ '.sys.database_principals WHERE name = ''' + lgz.name + ''') CREATE USER [' + lgz.name + '] FOR LOGIN [' + lgz.name + '] ;'
+ 'EXEC sp_addrolemember N''WhateverDEVAdmins'', N''' + lgz.name + ''' '
from master.sys.databases dbz
CROSS JOIN master.sys.server_principals lgz
where database_id > 4
and type_desc = 'WINDOWS_LOGIN'
and lgz.name like 'MyDomain\%'
Lowell
September 19, 2011 at 5:20 pm
thanks i'll taeka look at it. the issue is that the app for this does has to have a 1 to 1 so groups dont work....
September 20, 2011 at 1:17 pm
Here is a PowerShell 2.0 script that:
1. Reads a list of Windows users from a text file.
2. Creates a SQL Server login for that Windows user on an instance you choose.
3. Creates a database user for that SQL Server login in all available user databases.
4. Adds the new database user to a database role in each database.
You could add an outer loop that reads instance names from a file with little to no effort.
#load SQL Server snap-ins
Add-PSSnapin *SQL*
# set instance name. use DEFAULT for default instances, e.g. SVR3011\DEFAULT
$instance = "KSA0356460VD4\STD2005"
# set database role to add each user to
$db_role_name = 'db_owner'
foreach ($windows_user in Get-Content "C:\Users.txt")
{
$create_login_cmd = "
-- create 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),
@role_name SYSNAME ;
SET @cmd = N'' ;
SET @role_name = N'$db_role_name' ;
-- create user in all databases and add to
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''' + @role_name + ''', N''$windows_user'' ;
'
FROM master.sys.databases
WHERE database_id > 4
AND state_desc = 'ONLINE'
AND is_read_only = 0 ;
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
September 20, 2011 at 2:14 pm
thanks This should do the trick.
September 20, 2011 at 2:50 pm
hey so another question.
I may want to only select to add them to 1 database.
Can i change line 23 to use the db name and forgo the from and where statement that followed?
so there may be a few instances where i have several people to add but to only 1 db as opposed to all.
ah another question....the $instance variable is simply the servername\instance correct?
Thansk in advance for the help with this!
September 20, 2011 at 3:01 pm
RTSConsult (9/20/2011)
hey so another question.I may want to only select to add them to 1 database.
Can i change line 23 to use the db name and forgo the from and where statement that followed?
so there may be a few instances where i have several people to add but to only 1 db as opposed to all.
ah another question....the $instance variable is simply the servername\instance correct?
Thansk in advance for the help with this!
If you know your DB name then there is no reason to hit sys.databases. Check out this version:
#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"
$db = "DBNAME"
$db_role_name = "db_owner"
foreach ($windows_user in Get-Content "C:\Users.txt")
{
$create_login_cmd = "
-- create 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) ;
-- create user my database and add the user to a db role
SET @cmd = 'USE $db ;
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'' ;
' ;
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
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply