Granting Permissions using SQL 2005 Schema...

All,

I have been asked to grant a Windows group Full access to all tables under our Sandbox Schema. This will allow these users to do anything to the tables under this Schema.

I created the Windows Group (Sandbox Users), created the login in SQL, created the user in the database that is tied to the Windows group, then ran GRANT CONTROL ON SCHEMA::[Sandbox] TO [Sandbox Users].

I have verified that the users are in the Windows group, but they state that they still can not delete tables under the Sandbox Schema.

Anyone have any ideas?

Thanks,

Justin

They would need alter schema to drop tables in the schema.

GRANT ALTER ON SCHEMA::[Sandbox] TO [Sandbox Users]

-Sue

|||

CONTROL should cover ALTER and DELETE. My guess is that the users from that group have been denied some permission that is affecting their DELET statements.

You can make use of fn_my_permissions and has_perm_by_name to find out the actual permissions on the object, for example:

-- Connected as/impersonating a member of Sandbox users

--

SELECT * FROM fn_my_permissions( 'Sandbox', 'schema' )

go

SELECT has_perms_by_name( 'Sandbox.SampleTable', 'object', 'DELETE' )

go

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks for catching that Raul...not even sure what I was thinking last night. Or not thinking at that moment.

-Sue

|||

Raul,

Thanks... that will help. I will be working with the user this morning to see if I can figure out why he is having this problem.

It looks like the permissions are fine. I was helping him out this morning and I think he has an issue with the package he was trying to run, bu the fn_my_permissions helped out tremendously.

Thanks!

Justin