SQL Server Performance Forum – Threads Archive
Individual Database Security
I want to Put a security password and user level prvilages on a database., I mean that If my database backup restored on another machine, these security settings have to be satisfied for operating my databse. For Ex. On Machine 1sa password is "ABC" database backup taken from Machine 1 and Restored On Machine 2 On Machine 2
sa Password is "XYZ" Here, I am not interested to my database become operational, because the password is different. How can I do this ?
Sa password is for each SQL server you have.
For Ex.
If you have 2 SQL on same box, ( 2 SQL instances) each has different password if you want.
If you have one SQL per box, each may have same or differente password. So, password is at SQL level not at database level.
In short: In your example, database restored on Machine 2 became operational because Login sa is SQL property and no database property.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
The only option would be to have a third party product which encrypts and password protects a database backup…? This would still mean that once it was restored it would be operational… no way around that one… Cheers
Twan
If you want to secure your backup database
use BACKUP DATABASE WITH PASSWORD…. Oryan
Of course you can use a backup password, but anyone that needs to perform a backup or a restore needs to know the password. Third party encryptionizer can automatically encrypt a backup as it is being created. This allows an additional layer of encryption, which the backup operator does not need to know the key for. What’s more, if someone takes the backup media and tries to restore your database to a different installation of SQL Server, it will appear as an unreadable backup. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Thanks everybody, thanks oryand and satya, I am satisfied but one problem exists that, I applied user level previlages on my databse, shall I can apply it on my restore.
A backup databse contain all objects in the database including user level previlages,
After restored On Machine 2, fix the matching between users and logins,
try to use sp_change_users_login (more details in BOL) (I suggest you to manage user level previlages in ROLES – create a new role for each set of previlages) Oryan
Thanks oryand, I will try it.
If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database. And members of the db_owner fixed database role do not have RESTORE permissions.
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com
May be I missing something here: Original post: "Here, I am not interested to my database become operational, because the password is different." If the idea is to have database in other box but not restored or restored with password, agree with all.
But restored and no operational? How?
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
You can restore database as non-operational by the way of using with NORECOVERY where database will be LOADING state until you restore last Transaction log or specify WITH RECOVERY option.. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Ok. Now I understand.
Thanks. Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
]]>