Re: assigning DB-user to server role

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 01/27/04 

Date: Tue, 27 Jan 2004 08:00:50 -0600

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: 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: assigning DB-user to server role
    ... You script seems to promise what we expected when we used the Enterprise ... > You should be able to grant object permissions to the roles only. ... > EXEC sp_addrole 'MyRole' ...
    (microsoft.public.sqlserver.security)
  • Re: group vs individual user security
    ... > If you create a user & grant him permissions, ... In order to work with the systems tables, all users must be a member of the ... Users Group ...
    (microsoft.public.access.security)
  • Grant a domain user read-only access to AD 2003
    ... I've created a new user who is a member of the "Domain users" group ... I want to grant this user read only permissions to the whole of ... the very top level, granting "read only" permissions. ...
    (microsoft.public.windows.server.active_directory)