I have a SQL server with multiple databases( say Database A to E) on a same
instance.
This is my scenario:
I have an application, say application A, which read/writes to Database A, b
ut also read-only data from B,C,D and E database. Currently, I have given a
user, say User A, full access right(both read/write) on all these databases
.
If I want to tight up the security by enforcing read/write on the A database
, but read only on the B,C,D,E database for user A by the use of user define
d role, is it possible? This way, any new recruit is hired, I will just add
the role to the new user.
However, I have trouble trying to define a database role, which will give me
options to assign access rights or permissions on multiple databases.
Any suggestions?
Thank you!
MartinHi
If you access the read only database through views, stored procedures or
user defined functions then the owner of the view/stored procedure/UDF will
require the access.
See "Using Ownership Chains" in Books Online:
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\adm
insql.chm::/ad_security_4iyb.htm
If you can make the database read only then this will make everything more
secure.
John
"Martin" <anonymous@.discussions.microsoft.com> wrote in message
news:EC7A94A0-5283-475B-9651-2360DA0DF075@.microsoft.com...
quote:
> Dear support,
> I have a SQL server with multiple databases( say Database A to E) on a
same instance.
quote:
> This is my scenario:
> I have an application, say application A, which read/writes to Database A,
but also read-only data from B,C,D and E database. Currently, I have given
a user, say User A, full access right(both read/write) on all these
databases.
quote:
> If I want to tight up the security by enforcing read/write on the A
database, but read only on the B,C,D,E database for user A by the use of
user defined role, is it possible? This way, any new recruit is hired, I
will just add the role to the new user. However, I have trouble trying to
define a database role, which will give me options to assign access rights
or permissions on multiple databases.
quote:|||Thanks John!
> Any suggestions?
> Thank you!
> Martin
>
No comments:
Post a Comment