assigning DB-user to server role

Hello NG,
each time we assign users to roles, we have to additionally grant each
access right of each function of each table manually to this new user. Of
course we'd expect this to be done by the assignment itself.
Either we don't understand the servers's security philosophy correctly or
there should be another procedure to grant users access to the database via
the role, which has already defined access rights.
Thankful for any hint ...
MarcYou should be able to grant object permissions to the roles only. Member
users will inherit permissions via role membership. If a user is a member
of multiple roles, granted permissions are cumulative. However, denied
permissions take precedence.
I'm not sure how you've setup your users but the script below illustrates
this technique.
USE MyDatabase
--setup role security
EXEC sp_addrole 'MyRole'
GRANT SELECT ON MyTable TO MyRole
--setup user security
EXEC sp_grantlogin 'MyDomain\MyUser'
EXEC sp_grantdbaccess 'MyDomain\MyUser'
EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"M.Staiger" <mstaiger@.informatik.uni-siegen.de> wrote in message
news:40164c5c$1@.si-nic.hrz.uni-siegen.de...

quote:

---

> Hello NG,
> each time we assign users to roles, we have to additionally grant each
> access right of each function of each table manually to this new user. Of
> course we'd expect this to be done by the assignment itself.
> Either we don't understand the servers's security philosophy correctly or
> there should be another procedure to grant users access to the database

---

via

quote:

---

> the role, which has already defined access rights.
> Thankful for any hint ...
> Marc
>

---

|||You script seems to promise what we expected when we used the Enterprise
Manager to assign users to roles. Why does it work through scripts and not
through "klick-it"?
Marc
"Dan Guzman" <danguzman@.nospam-earthlink.net> schrieb im Newsbeitrag
news:eK1m53N5DHA.2692@.TK2MSFTNGP09.phx.gbl...

quote:

---

> You should be able to grant object permissions to the roles only. Member
> users will inherit permissions via role membership. If a user is a member
> of multiple roles, granted permissions are cumulative. However, denied
> permissions take precedence.
> I'm not sure how you've setup your users but the script below illustrates
> this technique.
> USE MyDatabase
> --setup role security
> EXEC sp_addrole 'MyRole'
> GRANT SELECT ON MyTable TO MyRole
> --setup user security
> EXEC sp_grantlogin 'MyDomain\MyUser'
> EXEC sp_grantdbaccess 'MyDomain\MyUser'
> EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "M.Staiger" <mstaiger@.informatik.uni-siegen.de> wrote in message
> news:40164c5c$1@.si-nic.hrz.uni-siegen.de...
Of[QUOTE]
or[QUOTE]
> via
>

---

|||You should be able to do these same steps using EM as well. I find that SQL
scripts are a lot easier to post here that the GUI steps. My personal
preference is to use scripts for most tasks since these can be easily
reused.
Hope this helps.
Dan Guzman
SQL Server MVP
"M.Staiger" <mstaiger@.informatik.uni-siegen.de> wrote in message
news:40167e62$1@.si-nic.hrz.uni-siegen.de...

quote:

---

> You script seems to promise what we expected when we used the Enterprise
> Manager to assign users to roles. Why does it work through scripts and not
> through "klick-it"?
> Marc
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> schrieb im Newsbeitrag
> news:eK1m53N5DHA.2692@.TK2MSFTNGP09.phx.gbl...
Member[QUOTE]
member[QUOTE]
illustrates[QUOTE]
> Of
> or
database[QUOTE]
>

---

|||Perhaps if you used profiler while you recreate your scenario in EM (create
the roles, grant permissions to the role, add users to role, try to execute
your query as that user) then posted the tsql scripts that would help us
figure out what's going on.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.