Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
 Posted - 2009-06-05 : 08:10:04
|
| 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 pplications 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 happens 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! |
|
|
swekik
Posting Yak Master
126 Posts |
Posted - 2009-06-08 : 12:33:33
|
| "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???" -Here you need to give explicit permissions to the other databases for this user .He can access only database "db1" . |
 |
|
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
Posted - 2009-06-09 : 09:35:07
|
| Okay, i am try to narrow my question.Can "manu" access all the extended stored procedures in the master database???? |
|
|
|
swekik
Posting Yak Master
126 Posts |
Posted - 2009-06-10 : 12:00:17
|
quote:
Originally posted by frank.svs
Okay, i am try to narrow my question.Can "manu" access all the extended stored procedures in the master database????
Manu cannot access the extended sprocs .You need to give him access to the master database. |
|
|
|
Brillix
Starting Member
3 Posts |
Posted - 2009-06-11 : 10:33:47
|
| It seems to me that the user doesn't have permissions on it default database, change the default database for that user to a database he has permissions on.Oded Razwww.dbsnaps.comwww.orbiumsoftware.com |
|
|
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
Posted - 2009-06-16 : 23:07:40
|
| Thank You All! |
|
|
|
|
|
|
|
|
SQL 2005 Security