Re: Adds the group to the database But Security EM

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 09/18/05

  • Next message: robert: "Re: user account creation" 
    Date: Sun, 18 Sep 2005 11:26:52 -0500

    > I view the database with SQL Server Enterprise Manager in the database and
    > users
    > and notice that domain group is add (sp_addgroup) but this not applied to
    > the SQL Server security domain group.

    sp_addgroup adds a new database role. It is not used to grant an existing
    Windows group database access. Also, sp_addgroup is provided only for
    backwards compatibility. Use sp_addrole instead.

    > Not using the SQL Server Enterprise Manager. How do get the domain group
    > with
    > privileges applied to both places (database user , security).

    >From Query Analyzer:

    USE MyDatabase
    --grant group permissions to connect to SQL Server
    EXEC sp_grantlogin 'MyDomain\MyGroup'
    --grant group permissions to use this database
    EXEC sp_grantdbaccess 'MyDomain\MyGroup'

    To setup object security, you can either grant permissions directly to the
    Windows account or grant permissions to a SQL Server role and control
    security via role membership

    --grant object permissions directly
    GRANT SELECT ON MyTable TO [MyDomain\MyGroup]

    --grant object permissions to role
    GRANT SELECT ON MyTable TO [MyDatabaseRole]
    --add group to role
    EXEC sp_addrolemember 'MyDatabaseRole', 'MyDomain\MyGroup' 

    -- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Joe K." <Joe K.@discussions.microsoft.com> wrote in message 
news:229E0AAA-AEED-400D-B082-3425919A0F85@microsoft.com...
>I am trying to add domain group with sql server security privileges to a 
>SQL
> Server database.
>
> I view the database with SQL Server Enterprise Manager in the database and
> users
> and notice that domain group is add (sp_addgroup) but this not applied to
> the SQL Server security domain group.
>
> Not using the SQL Server Enterprise Manager.  How do get the domain group
> with
> privileges applied to both places (database user , security).
>
>
>
  • Next message: robert: "Re: user account creation"

    Relevant Pages

    • ADP, Application Role, and objects
      ... The above link is to an atricle on how to implement SQL Server Application ... After you connect with your ADP, fire a bit of code to set the ... third party tools to view the data on the same database. ... Scenario 1 - If I explicitly grant permissions on that object to the user ...
      (microsoft.public.access.adp.sqlserver)
    • Re: Execute Persmission denied on object sp_OACreate
      ... > SQL Server is creating a job behind the scenes. ... > permissions. ... > SA account password and gaining access to the database. ... >>> How can get a user permissions to execute these stored procedures ...
      (microsoft.public.sqlserver.security)
    • Re: Newbie: I dont understand user permissions for table access
      ... > My database is remote to my workstation. ... > tables/fields WITHOUT specifying anything in the permissions dialogs? ... >> HOW are you connecting to SQL Server? ... what rights/permissions have been granted to the PUBLIC role? ...
      (microsoft.public.sqlserver.server)
    • Re: Execute Persmission denied on object sp_OACreate
      ... SQL Server is creating a job behind the scenes. ... SA account password and gaining access to the database. ... > SQL Server doesn't check permissions on indirectly referenced objects as ... > the proxy account security context for non-sysadmin users from Enterprise ...
      (microsoft.public.sqlserver.security)
    • Re: Security question ..
      ... What I want to prevent is any access to the database accept through our ... application unless you have elevated permissions. ... Authentication, if he is smart enough to create an NT Auth ODBC connection ... passes through to the database or to use SQL Server authentication. ...
      (microsoft.public.sqlserver.server)
    • Quantcast