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

GRANT permission to lots of tables and sp to db user

Is it possible to grant permissions (select, insert, delete, update, exec)
to a db user to all tables and all stored procedures in a specific db in an
easy (lazy!) way?
I mean, except for clicking in all permission checkboxes in Enterprise
Manager or writing a huge sql script like
grant select, insert, delete, update
on mytable1
to myuser
grant select, insert, delete, update
on mytable2
to myuser
...
grant exec
on mySP1
to myuser
grant exec
on mySP2
to myuser
...
?
Is there another way, like
GRANT select, insert, delete, update
on AllMyTables
to myuser
GRANT exec
on AllmySP
to myuser
?You can use a script like to example below to grant mass permissions
according to your requirements.
SET NOCOUNT ON
DECLARE @.GrantStatement nvarchar(500)
DECLARE @.LastError int
DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
SELECT
N'GRANT ' +
CASE
WHEN OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsView') = 1 THEN
N'SELECT, INSERT, UPDATE, DELETE'
WHEN OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 THEN
N'SELECT'
WHEN OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 THEN
N'EXECUTE'
END +
N' ON ' +
QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].[nam
e]) +
N' TO MyRole'
FROM
sysobjects ob
WHERE
OBJECTPROPERTY([ob].[id], 'IsMSShipped') = 0 AND
(OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1)
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @.GrantStatement
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
Hope this helps.
Dan Guzman
SQL Server MVP
"Siri" <Siri@.discussions.microsoft.com> wrote in message
news:51FFF23E-3543-4A4C-B6FE-8FCE4026CCA3@.microsoft.com...
> Is it possible to grant permissions (select, insert, delete, update, exec)
> to a db user to all tables and all stored procedures in a specific db in
> an
> easy (lazy!) way?
> I mean, except for clicking in all permission checkboxes in Enterprise
> Manager or writing a huge sql script like
> grant select, insert, delete, update
> on mytable1
> to myuser
> grant select, insert, delete, update
> on mytable2
> to myuser
> ...
> grant exec
> on mySP1
> to myuser
> grant exec
> on mySP2
> to myuser
> ...
> ?
> Is there another way, like
> GRANT select, insert, delete, update
> on AllMyTables
> to myuser
> GRANT exec
> on AllmySP
> to myuser
> ?
>|||Thank you very much! This really helped!
Siri
"Dan Guzman" wrote:

> You can use a script like to example below to grant mass permissions
> according to your requirements.
> SET NOCOUNT ON
> DECLARE @.GrantStatement nvarchar(500)
> DECLARE @.LastError int
> DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
> SELECT
> N'GRANT ' +
> CASE
> WHEN OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsView') = 1 THEN
> N'SELECT, INSERT, UPDATE, DELETE'
> WHEN OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 THEN
> N'SELECT'
> WHEN OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 THEN
> N'EXECUTE'
> END +
> N' ON ' +
> QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].&#
91;name]) +
> N' TO MyRole'
> FROM
> sysobjects ob
> WHERE
> OBJECTPROPERTY([ob].[id], 'IsMSShipped') = 0 AND
> (OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1)
> OPEN GrantStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM GrantStatements INTO @.GrantStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
> EXECUTE sp_ExecuteSQL @.GrantStatement
> END
> CLOSE GrantStatements
> DEALLOCATE GrantStatements
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Siri" <Siri@.discussions.microsoft.com> wrote in message
> news:51FFF23E-3543-4A4C-B6FE-8FCE4026CCA3@.microsoft.com...
>
>

Grant on all tables

Hi,

is there a way to grants object privileges on
all tables of database to an user?

like this:

grant select, insert, update, delete on <all tables> to usernamedb
go

thanks!!!!

No. You can add user to db_datareader and db_datawriter roles. This will however provide SELECT, INSERT, UPDATE and DELETE permission on tables/ views / table-valued functions etc. See Books Online for more details on the permissions / roles. Also, you should create a group/role and grant permissions to it instead of directly to the user. This is easier to manage and control.

Alternatively, you can write few lines of code that loops through the desired objects and grants necessary permissions on each object using dynamic SQL.

|||

In 2005 you can grant access to a schema's set of objects:

grant select, insert, update, delete on schema::dbo to bob

For example, in the AdventureWorks DB:

use adventureWorks
go
create user bob without login
go
--first prove no accss
execute as user='bob'
go
select * from production.product --will error
go
revert
go
grant select, insert,update, delete on schema::production to bob
go
execute as user='bob'
go
select * from production.product --will work
go
revert
go

Note that this gives access to table valued user-defined functions also...

|||Very cool... thank u

grant DELETE Global Temp Table permission to users

I suppose I have to do it in the tempdb database. I create a new user in thi
s database and add him to the db_owner role. Everything seems to work fine.
After restart SQL Server the users are dropded in the tempdb database.
Which is the correct way to grant these rights to a user?
Thanks."Edi Fellmann" <anonymous@.discussions.microsoft.com> wrote in message
news:E0499352-2B5D-46C4-A38E-34FC85B5BEC9@.microsoft.com...
> I suppose I have to do it in the tempdb database. I create a new user in
this database and add him to the db_owner role. Everything seems to work
fine.
> After restart SQL Server the users are dropded in the tempdb database.
> Which is the correct way to grant these rights to a user?
Tempdb is designed to be used by SQL Server as temporary storage and work
space. Any contents of tempdb are reset when SQL Server is restarted.
Consider creating a "temp" database for this purpose.
Steve|||I use the global temp table in a sp with dynamic SQL. It is not posible to u
se temp tables in this sp.
Is there any other solution?
Thankssql