Very Basic SQL Sever 2005 Security Question

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.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Very Basic SQL Sever 2005 Security Question

Author

Topic

maikeru-sama
Starting Member

4 Posts

Posted - 2009-10-07 : 21:32:57

First, if this is the wrong forum, I apologize.

I have created a View in my database. I want to give a developer outside of our department access to this view. I want to create a Database Role and place the user's domain account into this role.

I want the role to only allow the user to SELECT from the view I created. I don't want users in this role to be able to do anything else (View tables, stored procedures, other views etc etc) accept SELECT from this view.

I am not sure how to setup the role and what the best practice is.

Any help would be appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-08 : 00:45:46

1. Create the role
2. Add the domain account to the role
3. Grant SELECT permission to the view for the role: GRANT SELECT ON SomeView TO SomeRole

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

maikeru-sama
Starting Member

4 Posts

Posted - 2009-10-08 : 07:14:35

tkizer, what schema should I choose for the role?

maikeru-sama
Starting Member

4 Posts

Posted - 2009-10-08 : 09:00:23

I believe I have accomplished this task.

When I created the new Database Role, I didn't choose a Schema Owner and it defaulted to "dbo".

In Database Roles, inside Securables, I choose "All objects of type" and "Database" and specifically denied UPDATE, EXECUTE, DELETE AND INSERT

I will try to find a good book this weekend and try to understand alot more.

Thanks for the help

maikeru-sama
Starting Member

4 Posts

Posted - 2009-10-08 : 09:16:13

One question, the user seems to be able to SELECT on any view in the database (including systems views). Do I have to manually go through and specify deny select on every view in the database (except for the View I want them to access)?

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-10-09 : 10:43:02

I'd question if there was a need to create the role if it was just for one person. Or are there going to be more people added

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-10-09 : 10:45:10

I'd question if there was a need to create the role if it was just for one person. Or are there going to be more people added.

if you only want one person then if just add the same as tara has but just using the domin account instead of the role name

i.e.

Grant SELECT on [view] to [domain\login]

--------------------------
SQL Server MCP & MCTS

jayblaze2
Starting Member

5 Posts

Posted - 2009-10-09 : 12:27:32

maikeru-sama: change the schema from dbo to guest. you are giving the user Database Ownership by giving the user schema dbo.

Heres how i would do it; i wouldnt even creat a role since its for one person:

1. create the user
2. under "General" click "Securables"
3. Click the button "Add" located under whitbox (Securable section)
4. Select "Specific Objects"
5. In new window (popup) click object types
6. Select the ojbects you will want this person to use (you can narrow it down later; you'll see) click ok
7. Now click the browse button in the Select Objects Window
8. Now narrow downt the exact objects you want to grant access; Click ok then ok again
9. Now grant the permissions

hope this helps.

"I caught a fleeting glimpse
Out of the corner of my eye.
I turned to look but it was gone
I cannot put my finger on it now
The child is grown,
The dream is gone." (Pink Floyd)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources