Grant All function giving errors

I'm migrating from 2000 to 2005, what is the best way to handle the following error:

The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity

The code is below:

DECLARE @.sp_name AS sysname;
DECLARE syscursor CURSOR FOR
SELECT name FROM sysobjects
WHERE (xtype = 'P' or xtype='V') AND ((status & 0x80000000) = 0);

OPEN syscursor;
FETCH NEXT FROM syscursor INTO @.sp_name;

WHILE (@.@.FETCH_STATUS = 0)
BEGIN
EXECUTE ('GRANT all ON ' + @.sp_name + ' TO Public');
FETCH NEXT FROM syscursor INTO @.sp_name;
END

CLOSE syscursor;
DEALLOCATE syscursor;

It is not an error, it is a deprecation warning. The grant still works, for now, but ALL will be removed in a future version of SQL Server.

In SQL Server 2005, we have introduced new permissions. ALL does not include these new permissions (otherwise, existing programs might end up running with higher permissions in SQL Server 2005 than they did in SQL Server 2000). So the warning is for letting you know that ALL doesn't really mean "all permissions" anymore.

You should replace GRANT ALL with specific grants of the permissions you want to GRANT, for example, in your case, you can just replace GRANT ALL with GRANT EXECUTE, as EXECUTE is the only permission implied by ALL on stored procedures.

For additional information on ALL, see: http://msdn2.microsoft.com/en-us/library/ms188371.aspx. Actually, now that I look at it, the stored procedure permissions list is incorrect, it should only include EXECUTE, as I mentioned above. I'll file a bug to have the documentation corrected.

Thanks
Laurentiu

|||Commands like "grant all to admin" used to grant all permissions for all objects available in the database. What is the equivalent of this in SQL Server 2005 (it does not need to grant the new CONTROL right in my case)?

|||

CONTROL on the database is probably the closest. You can also make admin a member of db_owner. But there is no real replacement for ALL.

Thanks
Laurentiu

sql