Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Utilizing privileges of AD security group membership

Inheriting server-level privileges

AD users that are members of given AD security group will inherit server-level privileges granted to the mapped Windows group login. For example, consider the accounts-group AD security group, that is granted membership to the sysadmin server role on the Babelfish. You can inherit the server-level privileges using the following command:

1> ALTER SERVER ROLE sysadmin ADD MEMBER [corp\accounts-group];
            

Consequently, any Active Directory user who is a member of the accounts-group AD security group will inherit the server-level privileges associated with the sysadmin role. This means that a user like corp\user1, being a member of accounts-group, will now have the ability to perform server-level operations within Babelfish.

Inheriting database-level privileges

To grant the database level privileges, a database user has to be created and mapped with Windows group login. AD users which are members of given AD security group will inherit database-level privileges granted to that database user. In the following example, you can see how database level privileges for Windows group [corp\accounts-group] are assigned.

1> CREATE DATABASE db1; 
2> GO
1> USE db1;
2> GO
Changed database context to 'db1'.
1> CREATE TABLE dbo.t1(a int);
2> GO
            

Create a database user [corp\sales-group] for Windows group login [corp\accounts-group]. To perform this step, connect through TDS endpoint using login who is a member of sysadmin.

1> CREATE USER [corp\accounts-group] FOR LOGIN [corp\accounts-group];
2> GO
            

Now, connect as AD user user1 to check access of table t1. Since we have not yet granted the database level privileges, it will result in permission denied error.

1> SELECT * FROM dbo.t1;
2> GO
Msg 33557097, Level 16, State 1, Server db-inst, Line 1
permission denied for table t1
            

Grant SELECT on table t1 to database user [corp\accounts-group]. To perform this step, connect through TDS endpoint using login who is member of sysadmin.

1> GRANT SELECT ON dbo.t1 TO [corp\accounts-group];
2> GO
            

Connect as AD user user1 to validate the access.

1> SELECT * FROM dbo.t1;
2> GO
a
-----------

(0 rows affected)