Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2007

    Unanswered: SQL 2005 Security doubt

    SQL 2005 Security ...

    Hi All,

    Am new to SQL Server 2005, need small help on security.

    I have created a new database "db1" as "sa" user.
    Apart from "db1" databases,i have some more datbases
    which belong to other Applications and i am having 4 system databases.

    Now, here is my question.
    I am creating a login say "Manu" under Security tab at Instance level with SQL Server Authentication. Before do this, i have also ensured that Instance is set as both "Windows and SQL Authentication mode".

    I have'nt given any server roles to "Manu" Login.

    Now expand the Security under "db1" database and created a user with same name mapped to the login manu and i have assigned "db_owner" database role to him.

    Now, i should able to create new tables,views,sp's and other database objects...

    But while am trying to connect to the database "db1" using "manu" credentials from the Management Studio , it is throwing me an error saying that he don't have access to other databases ( i.e other applications databases.). This is one thing.

    And other doubt in my mind is, do we need to give any explicit GRANT to access System databases(master,model,tempdb,msdb) for the user "manu". Am very much confused in this aspect. Can anyone elaborate/comment on this???

    One More thing, we have some extended stored procedures inside master database.
    Question is do i need to give Explict GRANT EXEC privilge for all the extended stored procedures???

    What happends if i say,

    GRANT EXEC ON <storedprocname> TO PUBLIC;

    what is PUBLIC??? is this a role i.e. which is similar in ORACLE. That means if i give any privilege to PUBLIC , then it is be accessible to all users in that particular database!!

    Please correct me if am wrong.


  2. #2
    Join Date
    Feb 2004
    One Flump in One Place
    PUBLIC is one of those stupid MS roles that everyone inherits and you can't delete or disable. It grants permissions to several things. I actually run scripts to revoke the permissions given to PUBLIC.
    But yes - grant execute to public and everyone gets the permission.
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2007
    Thank You!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts