Need to automatically add a custom server role to any login created

Question

Need to automatically add a custom server role to any login created

random_name

SSC Veteran

Points: 275
More actions
February 24, 2014 at 12:41 pm

#289873

I have a need to automatically add a server role after any login is created. The idea so far is to make an all server ddl trigger for CREATE_LOGIN. However, I can't find examples of this being done. Is it possible? And any hints?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

probably we'll need more information.

creating server roles is available in 2012 and above, you probably mean "if i add a login, i want to add him to a server role(or database role?) automatically?

it really depends on the specifics, but using a DDL trigger is a possibility; but it's not going to be a recommended practice; if you are adding a new login, why not add the single line of code to add the user to a role right then? why do you need to do it automatically?

not everyone belongs in a server role.

CREATE LOGIN [mydomain\lowell] FROM WINDOWS;

EXEC sp_addsrvrolemember 'mydomain\lowell','sysadmin';

GO

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

random_name SSC Veteran Points: 275 More actions · Answer 2

You are correct, I do want to add a login to a server role. This is due to the new STIGs.

The extra line of code that you mention is what we are currently doing, and I don't want to have myself or all other dbas in the company needing to remember to add that extra line of code anytime we create a login.

JustMarie SSCertifiable Points: 7771 More actions · Answer 3

I'm a big believer in process and processes. In this case I would highly recommend making a stored procedure to create these logins and do your best to require them to be used.

That way you've got all the code in one place, everyone is doing things in the same manner, and you can add a bit of tracking if you so desire. It also makes it easier to change the process as needed.

It's not as efficient as being able to attach the role at account creation but it's something. ~resigned shrug~

random_name SSC Veteran Points: 275 More actions · Answer 4

I cannot claim credit for this solution, but it is what I needed.

Create trigger addLoginToServerRole on ALL Server

for CREATE_LOGIN

as

declare @data xml

declare @loginname nvarchar(1000)

declare @altercode nvarchar(1000)

set @data = eventdata()

select @data

select @loginname = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(1000)')

set @altercode = 'ALTER SERVER ROLE serverrolename ADD MEMBER ' + @loginname;

exec(@altercode)