The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 2,574 other followers

Adding the localized [BUILTIN\Administrators] as SQL Server and giving them SA equivalent rights

Posted by jpluimers on 2013/01/02

On development machines it can be comfortable to add the local Administrators group to SQL Server, and make them equivalent to SA (SQL Server Administrator).

This especially as SA is disabled by default when you install using Windows Authentication mode (which is the default). You can Change Server Authentication Mode to include SQL Server mode, but then you still have to enable SA (you can even rename SA)

This is basically what you want to do:

CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master];
EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\Administrators', @rolename = N'sysadmin';
GRANT CONTROL SERVER TO [BUILTIN\Administrators];

There are a few gotchas here:

  • The name of the group BUILTIN\Administrators depends on the localization of your system.
    This is one of the reasons I usually advise clients to have server systems run on the most common ENU (English USA) localization of Windows.
    Another reason is that it is far easier to find information ENU English Messages back on the internet.
  • You need to be SQL Server Administrator to begin with.
    There is a little trick to get this done: you can stop the SQL Server service, then restart SQL Server it in single-user mode.
    In single-user mode, members from the BUILTIN\Administrators group can connect as a member of the sysadmin fixed server role.
  • If you want to access SQL Server as member from BUILTIN\Administrators, you need to run your SQL client tools with the UAC elevated permission (otherwise the Administrative bit is not set, and the BUILTIN\Administrators is not recognized).

That’s what the batch file below solves.

You need to start it as member of BUILTIN\Administrators with elevated privilege (the easiest way is to run it from an elevated command prompt).

It will:

  1. check if it is running as Administrator
  2. translate BUILTIN\Administrators through the SID to the localization on that particular machine using PsGetSid.
  3. use a couple of batch files similar to find-sql-cmd.bat to find the location of the tools, and find which SQL Server instance to use
  4. restart SQL Server into single user mode (using the -f and -m startup options which – when using from NET START – need to be /f and /m)
  5. CREATE LOGIN FROM WINDOWS for the administrators group
    (you might want to turn off CHECK_EXPIRATION and CHECK_POLICY)
  6. Adds the administrators group to the sysadmin role using sp_addsrvrolemember
  7. Grants CONTROL SERVER to the login (which for sysadmin is not strictly needed, but for db_owner is)

You could extend it to check if SQL Server is indeed running in single-user mode.

This is the batch file:

@echo off
  "C:\Windows\system32\cacls.exe" "C:\Windows\system32\config\system" 1>nul 2>&1  && (goto :isAdmin)
:isNoAdmin
  echo you need to be Administrator, and (when under Vista or higher) run this using using UAC
  goto :exit

:isAdmin

  call %~dp0sql-set-sqlrun.bat

  echo stopping SQL Server %sqlservice% instance %sqlinstance% and starting it in single user mode
  net stop %sqlservice%
  :: -m == mono (single user mode)
  :: -f == minimal configuration
  :: "net start" requires the dashes to be replaced with slashes so -f -m becomes /f /m
  net start %sqlservice% /f /m
  pause
  setlocal

  :: http://support.microsoft.com/kb/243330
  call :withAdmins S-1-5-32-544

  endlocal
  pause
  echo stopping SQL Server %sqlservice% instance %sqlinstance% and starting it normal mode
  net stop %sqlservice%
  net start %sqlservice%

  echo.
  echo Note that after this batch file, in order to connect as member of [BUILTIN\Administrators],
  echo you need to run your SQL Server tool (for instance SQL Server Management Studio) under UAC
  echo i.e. with elevated permissions, otherwise SQL Server will not recognize the Administrator token.
  goto :exit

:withAdmins
  :: redirect stderr to null http://stackoverflow.com/questions/4507312/how-to-redirect-stderr-to-null-in-cmd-exe
  for /F "tokens=1,2 delims=:" %%i in ('psgetsid %1 2^> nul ^| find /v "%COMPUTERNAME%"') do (
  :: cannot remove spaces from %%j here
    call :withAdminsName %1 "%%i" "%%j"
  )
  goto :exit

:withAdminsName
  ::  echo SID=%1, Kind=%2, Name=%3
  :: %3 starts with a space after the double quote; remove it
  ::http://stackoverflow.com/questions/636381/what-is-the-best-way-to-do-a-substring-in-a-batch-file
  setlocal
  :: first strip the quotes
  set name=%~3
  :: strip the first character: copy from 1 till the end
  set name=%name:~1%
  echo localized SID %1 [BUILTIN\Administrators] in your language: [%name%]

  echo Adding [%name%] as a login, assigning it sysadmin and server privileges:
  %sqlrun% -Q "CREATE LOGIN [%name%] FROM WINDOWS WITH DEFAULT_DATABASE=[master];"
  %sqlrun% -Q "EXEC master..sp_addsrvrolemember @loginame = N'%name%', @rolename = N'sysadmin';"
  %sqlrun% -Q "GRANT CONTROL SERVER TO [%name%];"

  echo %sqlrun% -Q "CREATE LOGIN [%name%] FROM WINDOWS WITH DEFAULT_DATABASE=[master];"
  echo %sqlrun% -Q "EXEC master..sp_addsrvrolemember @loginame = N'%name%', @rolename = N'sysadmin';"
  echo %sqlrun% -Q "GRANT CONTROL SERVER TO [%name%];"

  endlocal

:exit

–jeroen

via:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: