July 3, 2008 at 8:10 am
Hi all,
Below statement works fine in 2000 what is equivalent in 2005?
SET Grant = 'GRANT ALL PRIVILEGES ON /* ' + @object_type + ' */
Thanks in advance
July 3, 2008 at 8:20 am
Au4848 (7/3/2008)
Hi all,Below statement works fine in 2000 what is equivalent in 2005?
SET Grant = 'GRANT ALL PRIVILEGES ON /* ' + @object_type + ' */ ' + @owner_name + '.' +'['+ @object_name + ']'+ 'TO DEv_user'
Thanks in advance
It should work on 2005 as well. Note that there object owners are schamas on 2005, and not users.
Regards,
Andras
July 3, 2008 at 9:05 am
There isn't a replacement. From the BOL:
ALL
This option is deprecated and maintained only for backward compatibility. It does not grant all possible permissions. Granting ALL is equivalent to granting the following permissions.
If the securable is a database, "ALL" means BACKUP DATABASE, BACKUP LOG, CREATE DATABASE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, and CREATE VIEW.
If the securable is a scalar function, "ALL" means EXECUTE and REFERENCES.
If the securable is a table-valued function, "ALL" means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.
If the securable is a stored procedure, "ALL" means EXECUTE.
If the securable is a table, "ALL" means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.
If the securable is a view, "ALL" means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 3, 2008 at 9:13 am
Au4848 (7/3/2008)
Andras the statment fails at 'GRANT ALL' which is deprecated in 2005 so I'm looking what can be used in place ALL
It is deprecated, but it still seems to work. E.g.:
CREATE TABLE a (a int)
go
CREATE USER me WITHOUT LOGIN
go
GRANT ALL PRIVILEGES ON dbo.a TO me
GO
SELECT *
FROM sys.database_permissions
WHERE major_id = OBJECT_ID('a')
Grant described it how to replace it properly with non deprecated statements, so in the future the script will not break.
- Andras
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply