We are trying to write a script to do the following task:
Create a new login and grant datareader to the master database on all SQL Server 2012 and 2014.
The script we have did what we want but for some reason, SSMS is complaining about the syntax saying the user already exists but it doesn't exist.
use master
go
IF EXISTS
(select * from sys.syslogins
where loginname = 'login_name')
(select 1 from sys.syslogins)
else
if
(select left(cast(SERVERPROPERTY('ProductVersion') as varchar),2)) = '12'
CREATE LOGIN [login_name] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
CREATE USER [login_name] FOR LOGIN [login_name]
ALTER ROLE [db_datareader] ADD MEMBER [login_name]
if
(select left(cast(SERVERPROPERTY('ProductVersion') as varchar),2)) = '11'
CREATE LOGIN [login_name] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
CREATE USER [login_name] FOR LOGIN [login_name]
ALTER ROLE [db_datareader] ADD MEMBER [login_name]