CODEWITHSUNDEEP
sql-server
fr21
fr21

Reputation: 1816

How to add an user to sysadmin role using ALTER ROLE?

'sa' user when executing the below script, the following error message is shown.

IF NOT EXISTS (SELECT LOGINNAME FROM MASTER.DBO.SYSLOGINS WHERE NAME = N'p_sys')
BEGIN
    CREATE LOGIN [p_sys] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
    ALTER ROLE sysadmin ADD MEMBER p_sys;
END

Msg 15151, Level 16, State 1, Line 4
Cannot alter the role 'sysadmin', because it does not exist or you do not have permission.

I do not want to use sp_addsrvrolemember because MS says it is deprecated.

Please help.

Upvotes: 6

Views: 22069

Answers (1)

Andomar
Andomar

Reputation: 238176

alter role is for database roles, but sysadmin is server role. Try alter server role instead:

ALTER SERVER ROLE server_role_name ADD MEMBER p_sys

Upvotes: 12

Related Questions