Help with query

Transact-SQL
1

Hello, and let me say on the spot that I'm literally brand new to MSSQL. I'm trying to create a "login check" and it's still processing if the login exists. Any help/direction on to fix this would be greatly appreciated :slight_smile:

The Error:
Executed as user: DOMAIN\USER. The server principal 'DOMAIN\OTHERUSER' already exists. [SQLSTATE 42000] (Error 15025). The step failed.

The Code:

IF NOT EXISTS 
    (SELECT name  
     FROM master.sys.server_principals
     WHERE name = 'DOMAIN\OTHERUSER')


USE [master]
GO
CREATE LOGIN [DOMAIN\OTHERUSER] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
USE [msdb]
GO
CREATE USER [DOMAIN\OTHERUSER] FOR LOGIN [DOMAIN\OTHERUSER]
GO
USE [msdb]
GO
EXEC sp_addrolemember N'SQLAgentOperatorRole', N'DOMAIN\OTHERUSER'
GO

Any light here, would be great :slight_smile:

2

Since this is a one-time execution, do you really need to check? If this is going to be included in a build script - then checking would be appropriate.

If you do need to check, then you will need to go with dynamic SQL. Here is a sample:

    Use master;
     If Not Exists (Select * From sys.server_principals sp Where sp.name = 'DOMAIN\otheruser')
  Begin
Declare @sqlCommand nvarchar(max) = 'Create Login [DOMAIN\otheruser] From Windows With default_database = master';
Execute sp_executeSQL @sqlCommand;
    End
     Go
     
    Use msdb;
     If Not Exists (Select * From sys.database_principals dp Where dp.name = 'DOMAIN\otheruser')
  Begin
Declare @sqlCommand nvarchar(max) = 'Create User [DOMAIN\otheruser] With default_schema = dbo';
Execute sp_executeSQL @sqlCommand;
    End

  Alter Role SQLAgentOperatorRole Add Member [DOMAIN\otheruser];

 --==== Any additional permissions
--       Grant Select On ... To [DOMAIN\otheruser];
1 Like
3

@bzidroglio - I'll also tell you that GO is a "batch separator". Except for any temp or real tables created, it's like running each group of code that's between the separators in a separate SSMS code window.

1 Like
4

@jeffw8713 Thanks much, worked as charm, had to use

EXEC sp_addrolemember N'SQLAgentOperatorRole', N'DOMAIN\OTHERUSER'

As your statement was giving me an error, other than that, perfect! :slight_smile:

And replying to your question, this is for a scheduled job, to counter some built-in cleanup on some logins, not a one time execution!

5

I am surprised the alter statement doesn't work - what edition/version of SQL Server are you running? Select @@version will return the version - and you may want to check compatibility level.

6

Not sure which version (got versions from 2008 up to 2019), but don't remember in which I was actually testing by the time. And the error had to do something with the "Add Member" bit. :slight_smile:

7

I don't recall which version included the alter role functionality. Glad it worked for you.

1 Like
8

I don't recall which version included the alter role functionality. Glad it worked for you.