Tuesday, October 25, 2005
SQL Server - Application role - II
Continuation of SQL Server - Application role - I...
Application role is one of the undervalued features available in SQL Server 2000.
I think most of the DBAs neglecting to utilize this feature in their SQL Server security setup. Some of the special characteristics of the application roles:
• Unlike other roles (fixed server roles, fixed database roles and user defined roles), application roles do not contain any members in it.
• To activate application role, it requires a password.
• When a user activates application role, his connection (only that session) get associated with the application role. Hence the session loses its all permissions and inherits permission from the application role.
• Application roles are database specific (the application roles are available only to the database they exist).
• Once it is associated with the standard SQL login (NT login or SQL Login), that SQL user session can not access the objects in other database.
• The associated session can access the other database objects only through GUEST granted permissions. Hence, if the guest user account does not exist in a database, the session can not gain access to the database.
• The associated session does not permit to execute “USE DATABASE” T-SQL command. Therefore, the session needs to address the other database object using three part naming convention.
Application role is one of the undervalued features available in SQL Server 2000.
I think most of the DBAs neglecting to utilize this feature in their SQL Server security setup. Some of the special characteristics of the application roles:
• Unlike other roles (fixed server roles, fixed database roles and user defined roles), application roles do not contain any members in it.
• To activate application role, it requires a password.
• When a user activates application role, his connection (only that session) get associated with the application role. Hence the session loses its all permissions and inherits permission from the application role.
• Application roles are database specific (the application roles are available only to the database they exist).
• Once it is associated with the standard SQL login (NT login or SQL Login), that SQL user session can not access the objects in other database.
• The associated session can access the other database objects only through GUEST granted permissions. Hence, if the guest user account does not exist in a database, the session can not gain access to the database.
• The associated session does not permit to execute “USE DATABASE” T-SQL command. Therefore, the session needs to address the other database object using three part naming convention.
# posted by Satheesh Chandra @ 10:31 AM 
