April 7, 2005 at 2:07 am
hi,
I am trying to put the users of my sql database in Read-only mode but for some reason it allows me to make inserts. The execution is successful and the role is shown as db_denydatawriter but it allows insertions. Please can someone suggest why this is happening ? Please help. this is urgent !!
I am executing the below statements to do this.
use northwind
exec sp_grantdbaccess 'rest','rtest'
go
exec sp_addrolemember 'db_denydatareader','rtest'
go
exec sp_addrolemember 'db_denydatawriter','rtest'
April 7, 2005 at 3:45 am
If you want to give read only permissions to the users in the database, you can give only one permission.
exec sp_addrolemember 'db_datareader','rtest'
go
Follow these steps : First create a login in the database.
Then create a user for that login and then give the user membership of db_datareader role. It should work.
--Kishore
April 7, 2005 at 4:21 am
Thanks for the reply. It is now working. I have another problem if anyone could help ...
I am connecting to SQL Server database from my application. When backups are performed, I am putting the database in single-user mode.
In the process the session are being killed I guess. Because of this the application is losing connection.
now The problem I have is that it is not getting the connection back when the database is set to multi user mode again. Even though I refresh the application the session is lost and the users have to re-login. Is there any way I can keep the session alive? I see this is not happening with the same application connecting to Oracle as backend database. With Oracle, once the database is back to multi user mode it is gaining the connection back.
Please can someone help in resolving this ? Also is there anyother way I can keep the session active and re-connect when the database is back.
Please help .. this is urgent. !!
April 7, 2005 at 4:46 am
SQL Server 2k allows hot backups. You need not put your SQL Server in single user mode to take the backups. SQL Server will perform the backups and also serve your clients.
Other thing regarding the connection is that, once you have put the database in single user, it will lose all the connections it had during that time. When the SQL Server is back in multi user mode, it does not restore back the connections. Connections will have to be reestablished to the database.
--Kishore
April 7, 2005 at 5:11 am
The point here is the backup of OS file system is also performed at the same time the bakcup of database is performed. so the file system is being put in Read-Only mode to avoid updation or inserts. Therefore it has become necessary to restrict database users also from making updations to synchronize the activity and avoid corruption of the data during restores.
Is there any other way you could suggest to achieve this. I am able to put the OS File system to Read-Only mode through some of our utilities developed for this purpose. But with MS SQL I am able to put to Read-Only or Single user mode but in either case it is not re-gaining the connections.
Any ideas on this ?
Thanks for your help and advice...
April 7, 2005 at 5:23 am
Why do you want to combine your SQL backups with system files ?
1. What is the size of your database ? 2. Is it a 24*7 enviornment ? 3. What is the peak time of your database ? 4. Where are you takiing your backups ? 5. What is the recovery model of your database ?
Provide me with the follwing information to help me serve you better.
--Kishore
April 8, 2005 at 8:21 am
just a note about db_datareader ...
db_datareader give a user permission to read any items
to block writes use db_denydatawriter which prevents users from writing to the database.
Curtis Smith
SQL Server DBA
Well in worked in Theory ...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply