Post reply

SQL 2005 Security Question

  • June 5, 2009 at 6:13 am

    #205507

    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 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.

    Thanks!

  • June 11, 2009 at 6:50 am

    #1007968

    Hi Mahesh,

    To start with the PUBLIC-role: you have is spot on. Any autorization to PUBLIC is for every login, since PUBLIC is a role every Login has.

    A db_owner for 1 database has no access to other databases; you need to grant that access to a specific user for each database.

    As a dbo you can use the system procedures is the system databases, except for the extended procedures in the master: those need specific grants toother users then the SA.

    Hope this helps

    Greetz,
    Hans Brouwer

  • June 16, 2009 at 9:23 pm

    #1010613

    Thanks Hans!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply