• sej2008 (3/14/2013)


    yes I got your answer but still why public role exists when we have fixed database roles and user defined roles.what are the permission given to this role.

    It's like the "Everyone" Windows group. Members of public (i.e. everyone) inherits a base set of permissions all users need in order to work within the database. Things like SELECT on the Catalog View sys.tables.

    The public Role can be used to also grant everyone a permission to a User-defined Object. Say you had a Stored Procedure you wanted everyone to be able to execute. You have the option of granting EXEC on that Stored Procedure to public, although that practice is discouraged in favor or creating a User-defined Database Role, granting EXEC on the Stored Procedure to the new Role, and then adding public as a member of the new Role, i.e. do not change the set of permissions granted directly to the public Role.

    and regarding "sa" account when it is best to use this account.

    You would use the sa Login when you needed to access the database instance as an "Administrator." Ideally though, I recommend adding specific Windows Domain Logins to the sysadmin Fixed Server Role and disabling the sa Login for the reasons previously mentioned.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato