want to grant all to public for entire dbase | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

want to grant all to public for entire dbase

This doesn’t work… grant all
to public
go it says it was successful, but when I look at the permission on the table, it doesn’t show. Any quick way to grant this?
The public role is a special database role to which every database user belongs.
Captures all default permissions for users in a database. To protect against unauthorized data access, minimize the permissions granted to the public role. Instead, grant permissions to other database roles and to user accounts associated with logins. Run SP_HELPROTECT for information on roles on a database. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I restored an exact version of our database to a test database. The original had public permissions granted on all tables as per the specs of our off the shelf app that uses it. To do this change manually, I had to open the public role, go to permissions, and check each box for every table. All 2500 of them. There just be a faster way.
Let SQL do the leg work for you.. get it to generate a script for all objects you want to assign permissions to.
I am sure you will find ways to adapt this, but heres something to get you started. Cut and paste the output which can then be run in Query Analyser. you manually clicked 2500 ticks? I dont think Id stay sane after that! SET NOCOUNT ON
DECLARE @rolename varchar(255)
SELECT @rolename = ‘public’ SELECT ‘GRANT ALL ON ‘ + TABLE_SCHEMA + ‘.’ + TABLE_NAME + ‘ TO ‘ + @rolename FROM INFORMATION_SCHEMA.TABLES where TABLE_TYPE = ‘TABLE’ and TABLE_NAME NOT LIKE ‘sys%’
UNION
SELECT ‘GRANT ALL ON ‘ + TABLE_SCHEMA + ‘.’ + TABLE_NAME + ‘ TO ‘ + @rolename FROM INFORMATION_SCHEMA.TABLES where TABLE_TYPE = ‘VIEW’ and TABLE_NAME NOT LIKE ‘sys%’
UNION
SELECT ‘GRANT ALL ON ‘ + SPECIFIC_SCHEMA + ‘.’ + SPECIFIC_NAME + ‘ TO ‘ + @rolename FROM INFORMATION_SCHEMA.ROUTINES where SPECIFIC_NAME NOT LIKE ‘dt_%’

Great idea Chappy, I have one problem. In Northwind, this doesn’t work… GRANT ALL ON db:confused:rder Details TO public Is there syntax that can make this command understand the space in between Order and Details?
I figured it out. [] are needed. However, why wouldn’t something like this work? I get a syntax error. USE northwind
GO declare @file as sysname
DECLARE @rolename varchar(255)
/* define which files to select */
SET NOCOUNT ON
SELECT @rolename = ‘public’
declare c1 cursor for
SELECT ‘GRANT ALL ON ‘ + TABLE_SCHEMA + ‘.[‘ + TABLE_NAME + ‘]’ + ‘ TO ‘ + @rolename
FROM INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = ‘BASE TABLE’ and TABLE_NAME NOT LIKE ‘sys%’
/* open cursor and get first record */
open c1
fetch next from c1
into @file /* change owner */
while @@fetch_status = 0
begin
DECLARE @count as int
set @count = @count + 1
@file
fetch next from c1
into @file
end
close c1
deallocate c1
alternatively you can make public part of the db_datareader and db_datawriter database roles. This will ensure that they also have rights on any new tables/views Cheers
Twan
by saying ‘@file’ I think you are hoping to execute whatever SQL is in the variable @file. This cannot work.
Try defining file as nvarchar and exec sp_executesql @file
]]>