Post reply

sp_addsrvrolemember to add an account to sysadmin role of an instance.

  • February 9, 2012 at 1:52 am

    #255292

    I have written a T-SQL code which outputs the below lines of codes.

    DECLARE @ServiceaccountName1 varchar(250);EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER', N'ObjectName', @ServiceAccountName1 OUTPUT, N'no_output'; EXEC sp_addsrvrolemember @ServiceaccountName1, 'sysadmin'

    DECLARE @ServiceaccountName2 varchar(250);EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\MSSQL$APP1', N'ObjectName', @ServiceAccountName2 OUTPUT, N'no_output'; EXEC sp_addsrvrolemember @ServiceaccountName2, 'sysadmin'

    DECLARE @ServiceaccountName3 varchar(250);EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\MSSQL$APP3', N'ObjectName', @ServiceAccountName3 OUTPUT, N'no_output'; EXEC sp_addsrvrolemember @ServiceaccountName3, 'sysadmin'

    DECLARE @ServiceaccountName4 varchar(250);EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\MSSQL$APP4', N'ObjectName', @ServiceAccountName4 OUTPUT, N'no_output'; EXEC sp_addsrvrolemember @ServiceaccountName4, 'sysadmin'

    The code is executing correctly only for the last line, although the output shows 4 rows affected. That is the service account from @ServiceaccountName4 variable is added to sysadmin role of APP4 instance.

    Even if i execute the first 3 lines separately also, it does not work. It only works for APP4 instance.

    Any ideas or workaround ?

  • February 9, 2012 at 8:01 am

    #1445090

    Try tracing the process (sqlservr.exe) with process monitor and see what happens.

    -- Gianluca Sartori

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

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