Re: assigning DB-user to server role

From: M.Staiger (mstaiger_at_informatik.uni-siegen.de)
Date: 01/27/04 

Date: Tue, 27 Jan 2004 16:06:09 +0100

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...
> 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...
> > 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 ...
> > Marc
> >
> >
>
>

Relevant Pages

  • Re: Permission to Insert records for DB USERS
    ... To add to the response by Vyas, consider creating your own database ... EXEC sp_addrole 'Supervisors' ... GRANT SELECT ON MyTable TO Supervisors ... > I am a fresh developer for the SQL server and have no Idea> for the permissions on the database. ...
    (microsoft.public.sqlserver.security)
  • Re: assigning DB-user to server role
    ... You should be able to grant object permissions to the roles only. ... If a user is a member ... EXEC sp_addrole 'MyRole' ...
    (microsoft.public.sqlserver.security)
  • Re: Login account scripts
    ... EXEC sp_addlogin 'User', 'Password', 'DefaultDB' ... To allow permissions on tables, you can use fixed roles, ... also grant permissions on objects with the grant statement ... >in the default database? ...
    (microsoft.public.sqlserver.security)
  • Re: script object-level permissions
    ... for example if you grant a object a row ... When you script your objects with the "script object level ... >Are you sure the objects have permissions other than ...
    (microsoft.public.sqlserver.security)
  • Re: temporary permission
    ... The solution you propose will not work in login script, ... runs as the user (who would not have the permissions to grant themselves ... The logoff script might work depending on just what ... A startup script would have the permissions to grant the account ...
    (microsoft.public.security)