newbie questions on schemas, roles, users & logins

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

General SQL Server Forums

New to SQL Server Programming

newbie questions on schemas, roles, users & logins

Author

Topic

BitShift
Yak Posting Veteran

98 Posts

Posted - 2008-04-05 : 13:24:11

Sql 2005
I want to grate execute permissions on my stored procedures to a role. While creating the role, it asks for "schemas owned by this role".
To me, the schema is merely a namespace that allows you to group objects, but arent schemas such as db_datawriter roles that are central to the db and only admin type users should have ownership of these, correct ?

In a nutshell, I want to:
create a new role and assign a user to that role
with a stored procedure, grant execute permissions to this role

I was confused by the sql 2005 dialog that asks me to take ownership of roles such as db_datawriter, db_datareader etc, wouldnt that mess up other things with the database ?

help ...

I think its time I review all of the above items
role
user
login
schema

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-05 : 13:37:02

http://www.exforsys.com/tutorials/sql-server-2005/sql-server-permissions.html

http://www.sqlteam.com/article/understanding-the-difference-between-owners-and-schemas-in-sql-server

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-05 : 14:04:07

It is not necessary to put Schema owner while creating role.If you have SP with other schema,you need to include that as well.

BitShift
Yak Posting Veteran

98 Posts

Posted - 2008-04-05 : 14:17:12

Thanks, that clears up a few things.

elracorey
Starting Member

30 Posts

Posted - 2008-06-10 : 11:26:10

Hi there,

Is there a way to grant a user read and write access, but to deny delete?

The roles db_denydatawriter and db_datawriter don't appear to distinguish write from delete.

Any help would be greatly appreciated.

Thanks, Lee

Just because something doesn't do what you planned it to do doesn't mean it's useless.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources