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