Windows Authentication in SQL Server 2005

Few days earlier, I came to learn some very basic login stuffs while working on Windows Authentication Mode of SQL Server. My task was to disable the Windows Authentication Mode. This is really very simple but it may irritate you if you are not informed of following facts.

First of all, you can’t disable or drop [BUILTIN/Administrators] or [ComputerName/UserName] Windows Credentials if you are logged in to SQL Server using this account.

So, you have to enable SQL Server Authentication Mode if it has not been enabled yet. Moreover, assign SERVER ROLE to “sysadmin” for this SQL Server Account.

Finally, login to SQL Server using SQL Server Authentication Mode and you can disable or even drop [BUILTIN/Administrators] or [ComputerName/UserName] Windows Credentials to disable Windows authentication in SQL Server.

However, you can create Windows Credential using the following query. Just assign SERVER ROLE to “sysadmin” after creating the LOGIN.

CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

-- To Drop LOGIN
DROP LOGIN [BUILTIN\Administrators]

Thanks
A Rahim Khan

2010

04/28

CATEGORY

SQL Server 2005

TAGS

disable windows authentication
SQL Server
SQL Server 2005
sqlserver 2005
windows authentication

Write comment