January 24, 2019 at 8:56 am
I have about 60 SQL Servers and I have an AD group that has Developers in it. I'm trying to build a script that will loop through the user databases and create a user and assign permissions for that user so that I can quickly add a group to control Developer perms. I'm first checking if the Login at the Server level exists and making it if it doesn't.
IF SUSER_ID('[Domain\IT Developers]') IS NULL
CREATE LOGIN [Domain\IT Developers] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
DECLARE @Command VARCHAR(8000)
SELECT @Command = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'',''SSISDB'') BEGIN USE ? IF (SELECT DATABASE_PRINCIPAL_ID(''Domain\IT Developers'')) IS NULL
CREATE USER [Domain\IT Developers] FOR LOGIN [Domain\IT Developers];
CREATE USER [Domain\IT Developers] FOR LOGIN [Domain\IT Developers]
--add perms to the user in that DB context
EXEC sp_addrolemember N''db_datareader'', N''Domain\IT Developers''
EXEC sp_addrolemember N''db_datawriter'', N''Domain\IT Developers''
GRANT VIEW DEFINITION TO [Domain\IT Developers] END '
PRINT @Command
EXEC sp_MSforeachdb @Command
When I run this I get an error telling me that the user already exists, but it doesn't because I've looked in the SSMS UI on the instance I'm testing and I know it isn't there. Any suggestions or corrections to what I'm doing?
January 24, 2019 at 9:16 am
Try removing the square brackets from the first line. It's looking for that literal login name - square brackets and all.
John
January 24, 2019 at 10:06 am
John Mitchell-245523 - Thursday, January 24, 2019 9:16 AMTry removing the square brackets from the first line. It's looking for that literal login name - square brackets and all.John
The login creation part of this isn't where the issue is, but I removed the brackets from it anyway. Now I'm getting strange behavior where the user doesn't exist but I get a message that it does already exist. Yet, the users are added and tied to the login. I'm really confused about what's happening.
January 24, 2019 at 10:23 am
You have CREATE USER in the code twice (once IF'd and once not).
IF SUSER_ID('[Domain\IT Developers]') IS NULL
CREATE LOGIN [Domain\IT Developers] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
EXEC sp_MSforeachdb '
IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''SSISDB'', ''tempdb'')
BEGIN
USE [?]
PRINT ''?''
IF DATABASE_PRINCIPAL_ID(''Domain\IT Developers'') IS NULL
BEGIN
PRINT '' Creating user.''
CREATE USER [Domain\IT Developers] FROM LOGIN [Domain\IT Developers];
END /*IF*/
PRINT '' Adding all permissions for the user.''
EXEC sp_addrolemember N''db_datareader'', N''Domain\IT Developers''
EXEC sp_addrolemember N''db_datawriter'', N''Domain\IT Developers''
GRANT VIEW DEFINITION TO [Domain\IT Developers]
END /*IF*/
'
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 24, 2019 at 11:25 am
ScottPletcher - Thursday, January 24, 2019 10:23 AMYou have CREATE USER in the code twice (once IF'd and once not).
IF SUSER_ID('[Domain\IT Developers]') IS NULL
CREATE LOGIN [Domain\IT Developers] FROM WINDOWS WITH DEFAULT_DATABASE=[master]EXEC sp_MSforeachdb '
IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''SSISDB'', ''tempdb'')
BEGIN
USE [?]
PRINT ''?''
IF DATABASE_PRINCIPAL_ID(''Domain\IT Developers'') IS NULL
BEGIN
PRINT '' Creating user.''
CREATE USER [Domain\IT Developers] FROM LOGIN [Domain\IT Developers];
END /*IF*/
PRINT '' Adding all permissions for the user.''
EXEC sp_addrolemember N''db_datareader'', N''Domain\IT Developers''
EXEC sp_addrolemember N''db_datawriter'', N''Domain\IT Developers''
GRANT VIEW DEFINITION TO [Domain\IT Developers]
END /*IF*/
'
Thanks Scott. I apparently need better proofreading skills.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply