I have a stored procedure which migrate data from this database to another. To exclude any error, I want to make the database read only for every transaction except mine. I use SqlConnection and SqlCommand to run the script. Is there any way to do this?
Asked
Active
Viewed 217 times
-1
-
Grant your login the appropriate permissions, but deny them to all other logins. Be aware that members of the sysadmin role cannot be denied permissions. – gvee Apr 01 '15 at 14:37
-
Unfortunately our every process runs on behalf of sysadmin. I know it is not a good solution, but that is we have. – Kiss László Apr 01 '15 at 14:48
-
Can you put the database in Single-User mode? – Brian J Apr 01 '15 at 14:52
1 Answers
1
Set the database to Single-user Mode. When you put it in Single-user mode, then you have the only connection available. As long as you don't relinquish your connection, no one else can connect.
Be warned, this will close all existing connections by other users. But it will prevent other connections from being made.
More information: https://msdn.microsoft.com/en-us/library/ms345598.aspx
Brian J
- 694
- 1
- 21
- 34
-
It sounds good. But is there a way to exclude other connections to grab the database before mine? – Kiss László Apr 01 '15 at 15:07
-
@KissLászló I'm not sure that I understand your question. If you put it in Single-user mode, then you have the only connection available. As long as you don't relinquish your connection, no one else can connect. – Brian J Apr 01 '15 at 15:09
-
-
1