November 3, 2008 at 7:12 am
please tell me how to disable(prevent) a database from being attached to a sql server
November 3, 2008 at 7:17 am
Don't give anyone CREATE DATABASE permissions - and then no one will be able to attach a database.
But for anyone that has got sufficient permissions you cannot prevent a database from being attached.
November 3, 2008 at 7:34 am
the problem is:
when attach a secured database to another server the security is removed.
then how to prevent that?
November 3, 2008 at 7:37 am
Well, if you attached a database from another server then obviously the database wasn't secure. If it had been secure then you wouldn't have been able to get it from the other server.
In this instance your goal is to secure the database on the server. If no one can get the data files then no one can attach it.
November 3, 2008 at 7:41 am
when i attach the database file .mdf to another server and that database was secured the security removed, by secured i mean it has permissions.
November 3, 2008 at 8:18 am
Ok, so what are you trying to do? Are you trying to prevent someone from attaching a database, or are you trying to provide security for a newly attached database?
If it's the latter, then you need to account for the fact that the users in the database, will not be associated with any logins as the logins are stored in the master database. So you'll need to create the relevant logins and then associate them with the corresponding users in the database with sp_change_users_login.
Hope that helps.
November 3, 2008 at 8:52 am
I'm sorry Karl,
I feel that i buzzard you,
What i need is to prevent a user from attaching a database until he/she provide a user name and a password.
November 3, 2008 at 8:55 am
Unfortunately this isn't possible. If the person logged onto the SQL Server has the correct permissions they can attach a database and you cannot prevent it.
November 3, 2008 at 9:03 am
OK, thanks a lot Karl
Do you know what is happen internally in the server when a user attaches a database, i.e. is there any procedures called or any thing else?
Muhammad
November 3, 2008 at 9:04 am
Consider Transparent Database Encryption in SQL 2008. It's not username/password, but it will prevent a database from been attached/restored unless the appropriate certificate is on the destination server.
Now, if someone has admin rights to a server, they can back the certificate up and take a copy of the database, so it still won't stop an administrator from stealing the database. However if makes backups much more secure, providing the certificate is not backed up to the same place.
One big warning about that. If, for any reason, the certificate is lost, there is no way to access the database ever again.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 3, 2008 at 9:14 am
muhammed_annaggar (11/3/2008)
OK, thanks a lot KarlDo you know what is happen internally in the server when a user attaches a database, i.e. is there any procedures called or any thing else?
Muhammad
None that you can modify.
You can put a DDL trigger on the server to prevent people attaching a database (on create database), but that's pretty much the same as denying create database rights. Won't stop someone taking the DB elsewhere.
What's the problem that you're trying to solve here?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 3, 2008 at 9:42 am
the problem is the permissions are removed when attaching a database to an other server.
Muhammad
November 3, 2008 at 10:12 am
muhammed_annaggar (11/3/2008)
the problem is the permissions are removed when attaching a database to an other server.Muhammad
It should really only be the dba that is allowed to attach databases to a prod server, and a dba usually can bypass most permissions, it is dangerous to allow user to do this.
November 3, 2008 at 1:02 pm
muhammed_annaggar (11/3/2008)
the problem is the permissions are removed when attaching a database to an other server.Muhammad
Which permissions?
SQL's permissions work at 2 levels. There are the database-level permissions which are related to the objects within the database and are granted to the users in the database. Select on tables, execute on procs, alter on schemas, etc. Those permissions are stored within the database and will be transferred with the database.
You may, after attaching a database to a different server, have to reassociate the users within the database with the logins on the server. The sp_changeuserlogin procedure is used to do that
Server-level permissions are related to server-wide objects and are granted to the logins. That's rights like create database, backups, view server state, alter server state, greate login. Those are stored in the master database and if a user database is moved from one server to another, those permissions will not transfer with that user database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply