February 8, 2013 at 2:42 pm
I want to grant all TVPs for permission to public. I have a script that is like this for Sprocs
DECLARE @@tablename varchar(100)
DECLARE @myprocedure varchar(100)
DECLARE tnames_cursor CURSOR FOR (SELECT Name FROM SYSOBJECTS WHERE type = 'P' )
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @@tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SET @myprocedure = @@tablename
EXEC ('Grant Exec On ' + @@tablename + ' To [PUBLIC]')
END
FETCH NEXT FROM tnames_cursor INTO @@tablename
END
I would like to do the same with TVPs.
I seem to get the TVP's by doing this
SELECT * FROM SYSOBJECTS WHERE [TYPE]='TT'
The format is wrong. Is there anyway to retrieve TVP's from the system tables?
February 8, 2013 at 3:28 pm
SELECT * FROM sys.table_types
February 8, 2013 at 3:50 pm
Thank you very much. I must have been googling it incorrectly. Found a million pages of how to give permission/create a TVP but could not figure something that gave me that tidbit.
February 9, 2013 at 2:22 am
Instead of loop, you can just:
grant execute to public
That includes all procedures that will be generated in the future in that database and all scalar functions.
What would you like to grant to table type?
February 10, 2013 at 9:01 pm
Good point. I am laying these down all at once. If I run that command will it take care of the TVP's and Sprocs?
February 10, 2013 at 10:36 pm
It should, try it.
"public" is the database role that every db user is in. If a user emerges you don't want to give that right, you are in trouble. It is better to create a custom role. Also, granting object permissions at schema level is better practice than granting them at database level. If a new schema appears, they will not be able to access it unless you explicitly add that permission.
Final solution would be: create a custom role (as container for permissions), grant execute on specific schemes to that role, assign users to that role:
create role MyRole
grant execute ON schema::dbo, schema::othershema TO MyRole
EXEC sp_addrolemember MyRole, MyUser1
EXEC sp_addrolemember MyRole, MyUser2
...
February 10, 2013 at 10:58 pm
Vedran Kesegic (2/9/2013)
Instead of loop, you can just:
grant execute to publicThat includes all procedures that will be generated in the future in that database and all scalar functions.
What would you like to grant to table type?
I'm pretty sure that I'd never grant such a permission to the public. Why not create a "db_Executor" role in the database and GRANT EXECUTE TO db_Executor? Then you can grant individuals and groups the privs to execute everything.
I strongly recommend that you leave PUBLIC alone. It covers everyone and it's just too dangerous to grant some priv(s) to PUBLIC.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 10, 2013 at 11:08 pm
Jeff, you are repeating myself 🙂
February 15, 2013 at 1:35 pm
Sorry, had to be away from this for a bit. Thanks for the responses. Let me explain why I think "Grant to public" is needed. We don't own these DB's. I create the DB, and then package it in an installer. That installer is then run on a server outside my control. I have to guarantee that all users Standard/NT can access the sprocs, and TVPs. An admin can always make a new role if they feel like so public is not abused. Yes, I am opening this wide open since the application requires I do so.
February 15, 2013 at 2:08 pm
I am not sure if I follow how grant to execute works
grant execute to public
If i run that I do not get the desired result of the sprocs and user defined table types added to the database role Public
This is on an existing DB where public does not have any rights
When I run
GRANT EXECUTE ON TYPE::TempType To [PUBLIC];
GRANT EXECUTE ON SprocXXX To [PUBLIC];
I get the permission rights I would expect when looking at the role within the database. I think I need to explicitly loop to get all the members I need and give permissions
Am I wrong with this?
February 15, 2013 at 2:49 pm
Did you actually tried to execute a procedure or you concluded it doesn't work without trying because you do not see object grants as before?
February 15, 2013 at 2:58 pm
Vedran Kesegic (2/15/2013)
Did you actually tried to execute a procedure or you concluded it doesn't work without trying because you do not see object grants as before?
No I did not execute the sproc to see if I have permissions to execute it. i looked in the roles under public. It was not listed in there. Wouldn't it show up there? Why is there a difference between the two execute methods?
February 15, 2013 at 3:29 pm
"public" is a database role.
"grant execute to public" grants to public database role a permission to execute any executable object (procedure, scalar function) within a database. That is why there is no "ON objectname" there. Securable here is a whole database, not an object, and not a schema.
You will not see that permission in that wizard screen if it displays only object permissions, because it is not an object permission.
To see all database permissions you must query sys.database_permissions - read this.
February 15, 2013 at 4:09 pm
JKSQL (2/15/2013)
I create the DB, and then package it in an installer. That installer is then run on a server outside my control. I have to guarantee that all users Standard/NT can access the sprocs, and TVPs. An admin can always make a new role if they feel like so public is not abused. Yes, I am opening this wide open since the application requires I do so.
If admin creates a new role, that will not revoke grants you gave to the public database role. And just creating a role won't help because admin has to assign permissions to that role - does admin knows or wants to know which permission he has to assign? I don't think so. So, granting permissions to public certainly will abuse public, and make admins difficult to correct that.
In your scenario, you give permissions directly to public role, then db admins controls who can execute your procedures by assigning certain logins access to your db. Admin must choose login, click on your db to give access, and select the roles ("public" is selected by default and cannot be deselected). On that screen admin can add more roles to the db user, not just public, by ticking checkboxes.
Correct way would be you to create a db role and grant permissions to that role. The role will be restored together with the database, and admin can assign that role on the screen described few lines earlier. Your role will appear on that screen. Very simple for the admin.
It's almost as easy as granting directly to public, but is much more secure.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply