SQL Authentication ONLY

[agoeddeke]

Hello,

I normally install MSDE with my app and use SQL Authentication and do everything using the SA account. I had a situation recently where someone connected with Windows Authentication via MS Access and had full access to the database.

How can I disable the BUILTIN/Administrators account so that the ONLY way to connect to the server is through SQL authentication?

Thanks,
Andy

 

[mrdenny]

This will remove the sysadmin permissions normally granted to the Local Admin Group.

EXEC sp_dropsrvrolemember 'BUILTIN\Administrators', 'sysadmin'
[\code]

Denny

Between the ESP=ON and the RUM (Read Users Mind) upgrade, I'm ready to go.

 

[agoeddeke]

Thanks Denny,

I also stumbled across this in the BOL:

EXEC sp_denylogin [BUILTIN\Administrators]

Do you see any drawback to using this?

Thanks,
Andy

 

[mrdenny]

That will disable the logins, where as the command I sent will simply remove the sysadmin permissions.

Either one will do the trick.

Denny

Between the ESP=ON and the RUM (Read Users Mind) upgrade, I'm ready to go.