SQL Server Integrated Security (Windows Authentication)

Question

I have a question about Windows Authentication on SQL Server.

If I give my database to anyone, will they still be able to access it using Integrated Security?

Because it seems like if I move my database from one machine to another, I am still able to access it using Integrated Security. Does this mean anyone can access it using Integrated Security (Windows Authentication) if I give them the file?

EDIT: I'm just curious because I was working on my SQL Server file (.mdf) then I moved it to another machine and I was still able to access it using Windows Authentication

EDIT: Its an MDF file which I attach

Answer 1

If on this new machine you are logged into SQL Server and are, through any one of various means, a member of the sysadmin role, then absolutely you will have access to it. Anyone who is a member of the sysadmin role will have access to it.

For people who authenticate via Integrated Security / Trusted Connection and who are not sysadmin, then if there a User in the DB that is linked to a Login (in SQL Server) that is based on a Windows Group, then anyone in that Windows Group will still have access as long as the MDF is attached to an instance that is part of the same network such that the Security ID (SID) for that Windows Group is the same as the one listed in the database that connects that User to the Login (in [master]).

When using LocalDB, "deploying" to the customer will entail that they have an instance of LocalDB on their PC and you will attach this DB. They would be sysadmin on their local instance of LocalDB and as such should have access to any databases that are attached. Just keep in mind that the instance of LocalDB is per-windows-Login so only that Login on that PC will have the instance of LocalDB that has your database on it. It can be "shared" with others, but you would need to set that up separately.

However, the best way to know if this plan will or will not work is to try it out. Deploy your database to a coworkers PC and see if they (not you) can access the database. That is a better indication of what will happen then any answer here could give ;-). Even better would be to "deploy" it to a computer that is not even part of your network (which is the essentially the same scenario as the customer's PC would be, right?).

Answer 2

This is a slightly odd question - if you give someone your DB (however you do this) and have them set this up on a SQL server that they administer, then yes, they will have rights to access it.

If your database is then accessed by your application, you will need for them to set up the appropriate accounts to access the database as per their requirements.

Answer 3

As long as i know yes. If you give other you database, thy can always take ownership of the database and edit his permissions