Sql-server – Windows Login can’t access SQL Server even with SYSADMIN role

Whatever username you are using on the "remote" machine must be set up on the "server" machine.

So, if you are logged-in to "remote" as "John," there must be a "John" user on the machine that is hosting SQL Server 2008 Express, and unless you are going to provide different credentials, it should have the same password, too.

Individual accounts are going to be much harder to administer. They give you more granular control over access, but you can not keep out your Active Directory administrators. Anyone who is granted local administrator rights on your server can gain sysadmin access to the instance. And, as a very good DBA told me once, if you can't trust your domain admins you've got bigger problems on your hands.

By abstracting your access to AD groups, you gain two benefits:

1. Easier to manage access groups and rights based on roles, combined with stratified access control for other assets using those AD groups.
2. Provide more consistent auditing, as you will only need to show access by AD group and then who is contained within that group.