LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » SQL Server Database Administration » SQL Server Security » SQL Security 2005

SQL Security 2005

Options

Previous Topic · Next Topic

frank.svs

Posted: Friday, June 05, 2009 7:17:55 AM

Rank: Newbie

Joined: 11/20/2007
Posts: 7
Points: -79
Where do you live?: india

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.

Thanks!

Scott Whigham

Posted: Friday, June 05, 2009 9:50:57 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 466
Points: 1,020
Where do you live?: Dallas, TX

Given what you've told us, the configuration appears to be done correctly; it seems the most likely problem is that either (a) you *think* you configured it like you said but somewhere you missed something in the actual doing of the steps, (b) you are not connecting as ManU even though you think you are, or (c) you are connecting to a different instance from the one ManU's account is on. You do NOT need to explicitly grant system database access.

Public - the 'everyone' role; very similar to Oracle.

frank.svs

Posted: Tuesday, June 16, 2009 10:27:46 PM

Rank: Newbie

Joined: 11/20/2007
Posts: 7
Points: -79
Where do you live?: india

Thanks Scott!

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.