Setting role permissions on stored procedures

Question

Setting role permissions on stored procedures

barry555

SSC Eights!

Points: 944
More actions
August 16, 2006 at 9:48 am

#119321

I'm new to SQL Server 2005 and can't get my head around how to do the following:
I have set up three schemas, SchemaA, SchemaB and SchemaC owned by dbo. I have created a number of tables and stored procedures in each of these three schemas. I want to create a role that will have only execute permissions for stored procedures in the three schemas and no permissions on the tables (so basically users can only access the data through the sps)
When creating the role, on the General tab, who should I put as owner (and what is the impact of who it is), and should the role own the three schemas?
On the Securables tab, what do I do here?
I've tried creating a role, setting the owner to dbo and giving it ownership of the three schemas. I then went on to the Securables tab, added the stored procedures I wanted and clicked on Execute in the Explicit Permissions box. I then clicked OK and closed the dialog. However, when I opened the role's properties again, the Securables tab is blank!
Barry

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

I tested it and it is like that. I created everything: a schema, a table, a role exactly as Barry describes, went to Securables tab, added permissions to the role for the table, clicked OK and when I re-opened the Role prperties, securables window did not contain records. When I clicked Add and navigated to the table that I previously had given permissions for this role, none of the permissions were selected.

I did not have time to check actual syspermissions in an actual table, will do it later. It may be that the permissions are set, but the user interface does not retrieve them, just sets them.

Regards,Yelena Varsha

barry555 SSC Eights! Points: 944 More actions · Answer 2

Thanks Yelena, at least I'm not going mad!

It seemed so easy in SQL Server 2000. We're obviously missing something simple...

Regards,

Barry

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

Barry,

I think I got it.

I have 2 roles: Role1 and Role2. Both roles have DBO as the owner. Role1 owns Schema1 that contains Table1. If I try to grant Select on Table1 to Role1 using either the user interface Securables tab of Role properties as you did or using GRANT statement, it does not show anything at all if I run:

select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES

but if I grant permissions to Role2 then I can see a line in this view INFORMATION_SCHEMA.TABLE_PRIVILEGES that correspond the Select permission on Table1 to Role2. Now, guess who is the grantor? It is Role1 !!!! so the owner of the schema is the grantor, not myself who actually granted the permission. So I guess we can not grant the permission to the owner because he owns everything.

What was curios that the GRANT statement to give permission to the schema owner Role1 completed successfully, but did not actually add any permissions.

What else was strange that I can not see granted permission to Role2 and to another user in the user interface in the Securables tab of the Role2 properties or the user properties but I can see these 2 lines in the INFORMATION_SCHEMA.TABLE_PRIVILEGES I also can see the permissions in the table properties on Permissions tab.

I also would like to mention that I think that the GRANT statement changed:

GRANT SELECT ON OBJECT::Schema1.Table1 To myuser;

Regards,Yelena Varsha

barry555 SSC Eights! Points: 944 More actions · Answer 4

Thanks Yelena, you're a star!

As you rightly say, you can't assign permissions on an object which belongs to a schema owned by the same role/user.

Permissions in SQL Server 7 and 2000 seemed a lot easier!

Barry