February 9, 2015 at 9:40 am
Hello all,
I have migrated over 700 databases to another server and now I have to add a specific user to all these databases and sync , would anyone have a script to add this user at once to all these databases^
much appreciated
JR
February 9, 2015 at 9:52 am
the users themselves are contained in the databases themselves,
if you are migrating, maybe you mean migrating logins to a new server via the ms proc sp_help_revlogins?
for an existing instance, where i'm adding people to have access,
here's exactly what i sue for adding users and roles, across all databases.
i use a able for specific databases and a different table for my new users.
in my case, i add a role to every database, and add the user, then add the user to that role.
--#################################################################################################
--Create Table holding new users that will receive global read only access across ALL databases on this server
--#################################################################################################
IF OBJECT_ID('tempdb.[dbo].[#DBs]') IS NOT NULL
DROP TABLE [dbo].[#DBs]
CREATE TABLE #DBs (DBName varchar(128))
--all dbs?
INSERT INTO #DBs(DBName)
SELECT name from master.sys.databases WHERE database_id > 4 AND state_desc='ONLINE' AND source_database_id IS NULL
--specific dbs:
--INSERT INTO #DBs(DBName)
--SELECT 'DWH_Alpha ' UNION ALL
--SELECT'AccessAdmin' UNION ALL
--SELECT'UserAnalysis' UNION ALL
--SELECT'Zues'
IF OBJECT_ID('tempdb.[dbo].[#Users]') IS NOT NULL
DROP TABLE [dbo].[#Users]
CREATE TABLE #Users (UserID varchar(128))
INSERT INTO #Users(UserID)
SELECT 'mydomain\user1' UNION ALL
SELECT 'mydomain\user2' UNION ALL
SELECT 'mydomain\user3'
--#################################################################################################
--Create Users if needed
--#################################################################################################
DECLARE @Rollback int = 0
DECLARE @Commands VARCHAR(max)
IF @Rollback = 0
BEGIN
SELECT @Commands = s.Colzs
FROM(SELECT
Colzs = STUFF((SELECT ' ' + 'IF NOT EXISTS(SELECT * FROM sys.server_principals where type_desc=''WINDOWS_LOGIN'' AND name = ''' + UserID + ''')
CREATE LOGIN [' + UserID + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master];
'
FROM [#Users]
ORDER BY UserID
FOR XML PATH('')
),1,1,'')
) s
SELECT @Commands = REPLACE(@Commands,' ', CHAR(13));
PRINT @Commands
EXECUTE(@Commands)
--#################################################################################################
--GRANT VIEW SERVER STATE AND VIEW ANY DATABASE to these users
--#################################################################################################
SELECT @Commands = ' ' + s.Colzs
FROM(SELECT
Colzs = STUFF((SELECT ' GRANT VIEW SERVER STATE TO [' + UserID + '];
GRANT VIEW ANY DATABASE TO [' + UserID + '];
'
FROM [#Users]
ORDER BY UserID
FOR XML PATH('')
),1,2,'')
) s
SELECT @Commands = REPLACE(@Commands,' ', CHAR(13));
PRINT @Commands
EXECUTE(@Commands)
--#################################################################################################
--Create Role DevelopersReadAccess in all databases
--#################################################################################################
SELECT @Commands = s.Colzs
FROM(SELECT
Colzs = STUFF((SELECT ' ' + '
USE [' + name + ']
IF NOT EXISTS (select * from sys.database_principals where type_desc=''DATABASE_ROLE'' AND name = ''DevelopersReadAccess'')
BEGIN
CREATE ROLE DevelopersReadAccess
EXEC sp_addrolemember ''db_datareader'',''DevelopersReadAccess'';
END
'
FROM master.sys.databases
WHERE database_id >4
AND state_desc='ONLINE'
AND source_database_id IS NULL
AND name IN(SELECT DbName FROM #DBs)
ORDER BY name
FOR XML PATH('')
),1,1,'')
) s
SELECT @Commands = REPLACE(@Commands,' ', CHAR(13));
PRINT @Commands
EXECUTE(@Commands)
--#################################################################################################
--Add these users to the database and also the role DevelopersReadAccess in all databases
--#################################################################################################
SELECT @Commands = s.Colzs
FROM(SELECT
Colzs = STUFF((SELECT ' ' + '
USE [' + name + ']
IF NOT EXISTS (select * from sys.database_principals where name = ''' + t.UserID + ''')
BEGIN
CREATE USER [' + t.UserID + '] FOR LOGIN [' + t.UserID + '];
EXEC sp_addrolemember ''DevelopersReadAccess'',''' + t.UserID + ''';
END
'
FROM master.sys.databases db
CROSS JOIN #Users t
WHERE db.database_id > 4
AND db.state_desc='ONLINE'
AND source_database_id IS NULL
AND name IN(SELECT DbName FROM #DBs)
ORDER BY db.name
FOR XML PATH('')
),1,1,'')
) s
SELECT @Commands = REPLACE(@Commands,' ', CHAR(13));
PRINT @Commands
EXECUTE(@Commands)
END --IF
ELSE
BEGIN
--#################################################################################################
----Remove Users for the same definitions above
--#################################################################################################
SELECT @Commands = s.Colzs
FROM(SELECT
Colzs = STUFF((SELECT ' ' + '
USE [' + name + ']
IF EXISTS (select * from sys.database_principals where name = ''' + t.UserID + ''')
BEGIN
DROP USER [' + t.UserID + '];
END
'
FROM master.sys.databases db
CROSS JOIN #Users t
WHERE db.database_id > 4
AND db.state_desc='ONLINE'
AND source_database_id IS NULL
AND name IN(SELECT DbName FROM #DBs)
ORDER BY db.name
FOR XML PATH('')
),1,1,'')
) s
SELECT @Commands = REPLACE(@Commands,' ', CHAR(13));
PRINT @Commands
EXECUTE(@Commands)
--#################################################################################################
----Remove Logins for the same definitions above
--#################################################################################################
SELECT @Commands = s.Colzs
FROM(SELECT
Colzs = STUFF((SELECT ' ' + 'IF EXISTS(SELECT * FROM sys.server_principals where type_desc=''WINDOWS_LOGIN'' AND name = ''' + UserID + ''')
DROP LOGIN [' + UserID + '];
'
FROM [#Users]
ORDER BY UserID
FOR XML PATH('')
),1,1,'')
) s
SELECT @Commands = REPLACE(@Commands,' ', CHAR(13));
PRINT @Commands
EXECUTE(@Commands)
END
--select *
--FROM master.sys.databases
-- WHERE database_id >4
-- AND state_desc='ONLINE'
edit: fixed syntax
Lowell
February 9, 2015 at 9:55 am
johnnyrmtl (2/9/2015)
Hello all,I have migrated over 700 databases to another server and now I have to add a specific user to all these databases and sync , would anyone have a script to add this user at once to all these databases^
much appreciated
JR
windows login or sql login
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 9, 2015 at 9:56 am
Thanks for your response !
Just to clarify... I added a new server login on a new instance and I would only need to add this login to over 700 databases with a specific naming convention . Can this script to this ?
February 9, 2015 at 10:09 am
johnnyrmtl (2/9/2015)
Thanks for your response !Just to clarify... I added a new server login on a new instance and I would only need to add this login to over 700 databases with a specific naming convention . Can this script to this ?
Windows or sql login???
This may be useful
exec sp_msforeachdb @command1 = 'use [?]
IF (select name from sys.database_principals where name = ''a login'') IS NOT NULL
begin
alter user [a login] with login = [a login]
print ''Login altered''
end
else
begin
create user [a login] from login [a login]
exec sp_addrolemember ''db_datareader'', ''a login''
print ''Login created''
end'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 9, 2015 at 10:36 am
SQL login 🙂
So if database names are called *DBxx* and an existing user "ABC" would need to be a dbo of these.
February 10, 2015 at 4:54 am
johnnyrmtl (2/9/2015)
SQL login 🙂
My script above will work, don't forget to change the username and any roles you wish to grant
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 10, 2015 at 6:50 am
Perry Whittle (2/9/2015)
johnnyrmtl (2/9/2015)
Thanks for your response !Just to clarify... I added a new server login on a new instance and I would only need to add this login to over 700 databases with a specific naming convention . Can this script to this ?
Windows or sql login???
This may be useful
exec sp_msforeachdb @command1 = 'use [?]IF (select name from sys.database_principals where name = ''a login'') IS NOT NULL
begin
alter user [a login] with login = [a login]
print ''Login altered''
end
else
begin
create user [a login] from login [a login]
exec sp_addrolemember ''db_datareader'', ''a login''
print ''Login created''
end'
ok thanks ...
The user already exists with a sysadmin role , so it would look like this ?
exec sp_msforeachdb @command1 = 'use [?]
IF (select name from sys.database_principals where name = ''ABC'') IS NOT NULL
begin
alter user [ABC] with login = [ABC]
print ''Login altered''
end
--else
--begin
--create user [a login] from login [a login]
--exec sp_addrolemember ''db_datareader'', ''a login''
--print ''Login created''
February 10, 2015 at 6:55 am
Lowell (2/9/2015)
the users themselves are contained in the databases themselves,if you are migrating, maybe you mean migrating logins to a new server via the ms proc sp_help_revlogins?
for an existing instance, where i'm adding people to have access,
here's exactly what i sue for adding users and roles, across all databases.
i use a able for specific databases and a different table for my new users.
in my case, i add a role to every database, and add the user, then add the user to that role.
--#################################################################################################
--Create Table holding new users that will receive global read only access across ALL databases on this server
--#################################################################################################
IF OBJECT_ID('tempdb.[dbo].[#DBs]') IS NOT NULL
DROP TABLE [dbo].[#DBs]
CREATE TABLE #DBs (DBName varchar(128))
--all dbs?
INSERT INTO #DBs(DBName)
SELECT name from master.sys.databases WHERE database_id > 4 AND state_desc='ONLINE' AND source_database_id IS NULL
--specific dbs:
--INSERT INTO #DBs(DBName)
--SELECT 'DWH_Alpha ' UNION ALL
--SELECT'AccessAdmin' UNION ALL
--SELECT'UserAnalysis' UNION ALL
--SELECT'Zues'
IF OBJECT_ID('tempdb.[dbo].[#Users]') IS NOT NULL
DROP TABLE [dbo].[#Users]
CREATE TABLE #Users (UserID varchar(128))
INSERT INTO #Users(UserID)
SELECT 'mydomain\user1' UNION ALL
SELECT 'mydomain\user2' UNION ALL
SELECT 'mydomain\user3'
--#################################################################################################
--Create Users if needed
--#################################################################################################
DECLARE @Rollback int = 0
DECLARE @Commands VARCHAR(max)
IF @Rollback = 0
BEGIN
SELECT @Commands = s.Colzs
FROM(SELECT
Colzs = STUFF((SELECT ' ' + 'IF NOT EXISTS(SELECT * FROM sys.server_principals where type_desc=''WINDOWS_LOGIN'' AND name = ''' + UserID + ''')
CREATE LOGIN [' + UserID + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master];
'
FROM [#Users]
ORDER BY UserID
FOR XML PATH('')
),1,1,'')
) s
SELECT @Commands = REPLACE(@Commands,' ', CHAR(13));
PRINT @Commands
EXECUTE(@Commands)
--#################################################################################################
--GRANT VIEW SERVER STATE AND VIEW ANY DATABASE to these users
--#################################################################################################
SELECT @Commands = ' ' + s.Colzs
FROM(SELECT
Colzs = STUFF((SELECT ' GRANT VIEW SERVER STATE TO [' + UserID + '];
GRANT VIEW ANY DATABASE TO [' + UserID + '];
'
FROM [#Users]
ORDER BY UserID
FOR XML PATH('')
),1,2,'')
) s
SELECT @Commands = REPLACE(@Commands,' ', CHAR(13));
PRINT @Commands
EXECUTE(@Commands)
--#################################################################################################
--Create Role DevelopersReadAccess in all databases
--#################################################################################################
SELECT @Commands = s.Colzs
FROM(SELECT
Colzs = STUFF((SELECT ' ' + '
USE [' + name + ']
IF NOT EXISTS (select * from sys.database_principals where type_desc=''DATABASE_ROLE'' AND name = ''DevelopersReadAccess'')
BEGIN
CREATE ROLE DevelopersReadAccess
EXEC sp_addrolemember ''db_datareader'',''DevelopersReadAccess'';
END
'
FROM master.sys.databases
WHERE database_id >4
AND state_desc='ONLINE'
AND source_database_id IS NULL
AND name IN(SELECT DbName FROM #DBs)
ORDER BY name
FOR XML PATH('')
),1,1,'')
) s
SELECT @Commands = REPLACE(@Commands,' ', CHAR(13));
PRINT @Commands
EXECUTE(@Commands)
--#################################################################################################
--Add these users to the database and also the role DevelopersReadAccess in all databases
--#################################################################################################
SELECT @Commands = s.Colzs
FROM(SELECT
Colzs = STUFF((SELECT ' ' + '
USE [' + name + ']
IF NOT EXISTS (select * from sys.database_principals where name = ''' + t.UserID + ''')
BEGIN
CREATE USER [' + t.UserID + '] FOR LOGIN [' + t.UserID + '];
EXEC sp_addrolemember ''DevelopersReadAccess'',''' + t.UserID + ''';
END
'
FROM master.sys.databases db
CROSS JOIN #Users t
WHERE db.database_id > 4
AND db.state_desc='ONLINE'
AND source_database_id IS NULL
AND name IN(SELECT DbName FROM #DBs)
ORDER BY db.name
FOR XML PATH('')
),1,1,'')
) s
SELECT @Commands = REPLACE(@Commands,' ', CHAR(13));
PRINT @Commands
EXECUTE(@Commands)
END --IF
ELSE
BEGIN
--#################################################################################################
----Remove Users for the same definitions above
--#################################################################################################
SELECT @Commands = s.Colzs
FROM(SELECT
Colzs = STUFF((SELECT ' ' + '
USE [' + name + ']
IF EXISTS (select * from sys.database_principals where name = ''' + t.UserID + ''')
BEGIN
DROP USER [' + t.UserID + '];
END
'
FROM master.sys.databases db
CROSS JOIN #Users t
WHERE db.database_id > 4
AND db.state_desc='ONLINE'
AND source_database_id IS NULL
AND name IN(SELECT DbName FROM #DBs)
ORDER BY db.name
FOR XML PATH('')
),1,1,'')
) s
SELECT @Commands = REPLACE(@Commands,' ', CHAR(13));
PRINT @Commands
EXECUTE(@Commands)
--#################################################################################################
----Remove Logins for the same definitions above
--#################################################################################################
SELECT @Commands = s.Colzs
FROM(SELECT
Colzs = STUFF((SELECT ' ' + 'IF EXISTS(SELECT * FROM sys.server_principals where type_desc=''WINDOWS_LOGIN'' AND name = ''' + UserID + ''')
DROP LOGIN [' + UserID + '];
'
FROM [#Users]
ORDER BY UserID
FOR XML PATH('')
),1,1,'')
) s
SELECT @Commands = REPLACE(@Commands,' ', CHAR(13));
PRINT @Commands
EXECUTE(@Commands)
END
--select *
--FROM master.sys.databases
-- WHERE database_id >4
-- AND state_desc='ONLINE'
edit: fixed syntax
Sorry for the confusion ...
I have a user with sysadmin rights on an instance and I need to add him as dbo to a large amount of databases that were just migrated over to this instance .
February 11, 2015 at 4:05 am
if the user exists as a member of the sysadmin role you do not need to map them into databases, they can do what the hell they like anyway!!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 11, 2015 at 7:22 am
Perry Whittle (2/11/2015)
if the user exists as a member of the sysadmin role you do not need to map them into databases, they can do what the hell they like anyway!!
indeed 🙂 without having to add it to the db as a login.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply