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: Read the rest of this entry »
The Wiert Corner - irregular stream of stuff 