Using SQL Server Database Roles with AD-Groups

As I don’t bother with security issues on a regulary base, it happens that I forget the necessary steps. Therefore I use this blog post as a documentation and personal knowledge base…

  • Make sure that the schema your objects you want to grant access to, are within a schema that is owned by dbo. Otherwise you have to grant access to all objects used that are used by the respective object. Example: if a stored procedure queries a table than the access to the table would be necessary.
  • Create Login if necessary
    USE master;
    GO
    CREATE LOGIN [DOMAIN\UserGrp_MIS_XYZ] FROM WINDOWS;
    GO
  • Create User in BL
    USE [BL];
    GO
    CREATE USER [DOMAIN\UserGrp_MIS_XYZ] FROM LOGIN [DOMAIN\UserGrp_MIS_XYZ];
    GO
  • Create Role and assign User/Group
    CREATE ROLE ROLE_FOR_DOING_SOMETHING;
    GO
    EXEC sp_addrolemember 'ROLE_FOR_DOING_SOMETHING', [DOMAIN\UserGrp_MIS_XYZ];
    GO
  • Assign Secureables to Role – what should the role be allowed to do
    GRANT SELECT ON View TO ROLE_FOR_DOING_SOMETHING;
    GRANT EXECUTE ON USP_XYZ TO ROLE_FOR_DOING_SOMETHING;

Depending on the functionality of the object the Role grants access to, it may be necessary to create Users and Roles in other databases (like CORE or TOOL) as well. In that case repeat step 2 to 4.

Advertisements

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s