Making database read-only
-
How do I make a database "read-only" for a certain user/group? I need to setup the database such that only one user/group is allowed full control on it, and others can use the database as read-only. The settings should be such that any new tables/indices/views created should also become read-only automatically for those users.
Krishnan
-
sql2000 ?
only make the wanted user or group member of the database-owner group in the database. Database owners can do anything they want in the db.
exec sp_addrolemember N'db_owner', N'youruser'
Grant the other users only db-datareader authority.
exec sp_addrolemember N'db_datareader', N'yourreaduser'
This way they are not allowed to change anything in the db.
If you put the database in read-only state, noone can alter anything !
alter database yourdb set read_only
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Sorry, but I could not understand what you meant. I am using SQL2000 MSDE. Could you please elaborate?
Thanks!
-
What isn't understandable about that?
Group needing full rights - give them database owner permission.
All other groups - give them only datareader permission
-SQLBill
-
Using isqlw.exe (or osql.exe) (i cannot recall wich one is provided with msde) you can perform something like this :
create a script like this
-- allow serverlogin
exec sp_grantlogin N'windowsdomain\windowsuser_A' -- or (group)
-- allow dblogin
EXEC sp_grantdbaccess N'windowsdomain\windowsuserA', N'windowsuser_A'
-- grant all using db-owner group
exec sp_addrolemember N'db_owner', N'windowsuser_A'
------
-- allow serverlogin
exec sp_grantlogin N'windowsdomain\windowsuser_B'
-- allow dblogin
EXEC sp_grantdbaccess N'windowsdomain\windowsuser_B', N'windowsuser_B'
-- grant only read
exec sp_addrolemember N'db_datareader', N'windowsuser_B'
Then execute the script using isqlw
isqlw -Uyouradminuser -Pyouradminpwd -S yourserver -d master -i "yourscriptfile" -o "youroutputfile"
I hope this helps.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I tried the settings as you have specified above. However, the user whom I am making a member of the datareader role, is still able to execute update statements on the database tables. I even tried making him a part of the denydatawriter role. But that also did not work. Is it because the user is otherwise part of the NT administrators group?
Also, when I initially created my database, I did not create any explicit logins. Then how does SQL server know which users to allow logging in to the database in that case?
-
IMO your user has SA-authority !
The only way a non-sysadm user gets rights to a db when newly created, is when that user has rights on model-db ! (or model-db al full rights to public)
Check this settings, and check the way your user logs in !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks, it was exactly what you thought
. because my user was a part of BUILTIN\Administrators group, he was given the sysadmin authority. After I removed this group as a member of sysadmin role, my user has been denied write permissions. But, then this means that the group's rights were superceding the restrictions I have applied to the user. In this case, how do I allow certain users of the administrators group to have full control on the database, while certain users are allowed read-only? It would certainly be cumbersome to add individual users to the sysadmin role. Creating separate user groups in NT would be one option, but is there any way from SQL itself? -
indeed, once you are sa, you are a "god" for sqlserver.
You cannot deny sa anything !
If you remove builti\administrators, make sure your sqlserver (and agent) service account is a local windows account, or a domain account !
When a user/group is not sqlserver-sysadmin, then you can deny rights and it will work.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply