Re: assigning DB-user to server role

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

Date: Tue, 27 Jan 2004 19:09:00 -0600

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...
> 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: about automation tool
    ... SQL Server MVP ... > usefil for running all batch file? ... within a SQLCMD file to specify the external scripts in the desired order. ... > hi, my self gautam, i am a DBA. ...
    (microsoft.public.sqlserver.security)
  • Re: about automation tool
    ... party tool which is useful for running all object scripts at one time " OR " ... SQL Server MVP ... I am working on a sqlserver2005 database. ...
    (microsoft.public.sqlserver.security)
  • Re: Number of SQL Server logins
    ... Busy writing some scripts to test what really happens when the 2 tables hit ... Mike Epprecht, Microsoft SQL Server MVP ... >> limit per database. ...
    (microsoft.public.sqlserver.server)
  • Re: if statement?
    ... it's more helpful to provide CREATE TABLE and INSERT scripts for ... SQL Server MVP ... "Fab" wrote in message ... > Im trying to put a value in a colomn depending of the value of 3 other ...
    (microsoft.public.sqlserver.programming)
  • Re: Create table in schema
    ... SQL Server MVP ... Every time I try to grant control to the dds_pco_role, ... Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, ... I also got that when I ran grant alter on schema. ...
    (microsoft.public.sqlserver.security)