granting public permissions to another role

We need to revoke all insert/update/delete access from public. So this won't
affect users I wanted to create a new role and grant it all of these excess
permissions from public, then revoke the permissions from public. It looks
like there are thousands of grants that need to be revoked - can anyone thin
k
of a way to script this?
Thank you in advance.One method is to generate the script using Transact-SQL. You can tweak the
example below to generate the desired script. This example script ignores
system objects and doesn't handle column permissions.
SET NOCOUNT ON
SELECT
CASE [p].[protecttype]
WHEN 204 THEN 'GRANT '
WHEN 205 THEN 'GRANT '
WHEN 206 THEN 'DENY '
END +
CASE [p].[action]
WHEN 193 THEN 'SELECT'
WHEN 195 THEN 'INSERT'
WHEN 196 THEN 'DELETE'
WHEN 197 THEN 'UPDATE'
WHEN 224 THEN 'EXECUTE'
WHEN 26 THEN 'REFERENCES'
END + ' ON ' +
QUOTENAME(USER_NAME([o].[uid])) + '.' +
QUOTENAME([o].[name]) + ' TO ' +
QUOTENAME([u].[name]) +
CASE WHEN [p].[protecttype] = 204 THEN ' WITH GRANT OPTION' ELSE '' END
FROM
[sysobjects] AS [o]
JOIN
[sysprotects] AS [p] ON
[p].[id] = [o].[id]
JOIN
[sysusers] AS [u] ON
[p].[uid] = [u].[uid]
WHERE
OBJECTPROPERTY([o].[id], 'IsMSShipped') = 0 AND
[u].[name] = 'public'
Hope this helps.
Dan Guzman
SQL Server MVP
"Bobsie" <Bobsie@.discussions.microsoft.com> wrote in message
news:745289F7-F2CF-4079-8A35-66974CFA73F3@.microsoft.com...
> We need to revoke all insert/update/delete access from public. So this
> won't
> affect users I wanted to create a new role and grant it all of these
> excess
> permissions from public, then revoke the permissions from public. It looks
> like there are thousands of grants that need to be revoked - can anyone
> think
> of a way to script this?
> Thank you in advance.|||Thanks for the help - and when it comes to revoking the permissions from
"public" I could use a similar script using "revoke" instead of "grant"?
"Dan Guzman" wrote:

> One method is to generate the script using Transact-SQL. You can tweak th
e
> example below to generate the desired script. This example script ignores
> system objects and doesn't handle column permissions.
> SET NOCOUNT ON
> SELECT
> CASE [p].[protecttype]
> WHEN 204 THEN 'GRANT '
> WHEN 205 THEN 'GRANT '
> WHEN 206 THEN 'DENY '
> END +
> CASE [p].[action]
> WHEN 193 THEN 'SELECT'
> WHEN 195 THEN 'INSERT'
> WHEN 196 THEN 'DELETE'
> WHEN 197 THEN 'UPDATE'
> WHEN 224 THEN 'EXECUTE'
> WHEN 26 THEN 'REFERENCES'
> END + ' ON ' +
> QUOTENAME(USER_NAME([o].[uid])) + '.' +
> QUOTENAME([o].[name]) + ' TO ' +
> QUOTENAME([u].[name]) +
> CASE WHEN [p].[protecttype] = 204 THEN ' WITH GRANT OPTION' ELSE '' END
> FROM
> [sysobjects] AS [o]
> JOIN
> [sysprotects] AS [p] ON
> [p].[id] = [o].[id]
> JOIN
> [sysusers] AS [u] ON
> [p].[uid] = [u].[uid]
> WHERE
> OBJECTPROPERTY([o].[id], 'IsMSShipped') = 0 AND
> [u].[name] = 'public'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bobsie" <Bobsie@.discussions.microsoft.com> wrote in message
> news:745289F7-F2CF-4079-8A35-66974CFA73F3@.microsoft.com...
>
>|||Yes, Bobsie, the script I posted was developed to script existing
permissions. You'll need to modify it so that modified permission scripts
are generated instead.
Run the script once to extract the public permissions with your new role
hard-coded as the grantee instead of public'. The generated script will
looks something like:
GRANT SELECT ON MyTable TO MyNewRole
GRANT EXECUTE ON MyProc TO MyNewRole
Then run the script again with a hard-coded REVOKE instead of the GRANT/DENY
CASE statement so the second script generated will be like:
REVOKE SELECT ON MyTable TO public
REVOKE EXECUTE ON MyProc TO public
Be sure to review the generated scripts before running in your environment.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bobsie" <Bobsie@.discussions.microsoft.com> wrote in message
news:3949EB00-7D35-4EC1-9920-249ABF69BBEC@.microsoft.com...
> Thanks for the help - and when it comes to revoking the permissions from
> "public" I could use a similar script using "revoke" instead of "grant"?
>
> "Dan Guzman" wrote:
>sql