Re: How to set permissions for objects quickly

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 10/17/05


Date: Mon, 17 Oct 2005 08:00:53 -0500

SELECT permissions on all user tables and views can be assigned by adding
users to the db_datareader fixed database role. There is no such role for
executing procs but you can assign such permissions by creating your own
role and using a script like the one below to grant permissions on all
existing stored procedures:

SET NOCOUNT ON

DECLARE @GrantStatement nvarchar(4000)

DECLARE GrantStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
    N'GRANT EXECUTE ON ' +
    QUOTENAME(ROUTINE_SCHEMA) +
    N'.' +
    QUOTENAME(ROUTINE_NAME) +
    N' TO SpExecuteRole'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
    OBJECTPROPERTY(
        OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
            N'.' +
            QUOTENAME(ROUTINE_NAME)),
        'IsMSShipped') = 0 AND
    OBJECTPROPERTY(
        OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
            N'.' +
            QUOTENAME(ROUTINE_NAME)),
        'IsProcedure') = 1
OPEN GrantStatements
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM GrantStatements
        INTO @GrantStatement
    IF @@FETCH_STATUS = -1 BREAK
    BEGIN
       RAISERROR (@GrantStatement, 0, 1) WITH NOWAIT
       EXECUTE sp_ExecuteSQL @GrantStatement
    END
END
CLOSE GrantStatements
DEALLOCATE GrantStatements

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Pleo" <rx8@hotmail.com> wrote in message 
news:un0Y5Rw0FHA.1564@tk2msftngp13.phx.gbl...
> After I create a user (ref to database), then I need to assign 'select' &
> 'exec' rights at permissions for all objects. But there are over 1000
> objects. How can I set the permissions quickly? Can I do it at query
> analyzer?
>
> Alternatively, what is the best way to setup this if want to add / rename
> database username? Thanks.
>
> "Pleo" <rx8@hotmail.com> bl news:ON0lytv0FHA.404@TK2MSFTNGP09.phx.gbl 
> 
> g...
>> I'm not familiar sql. At enterprise server (sql2000) > security > logins 
>>  >
>> (want to change name here).
>> Anyway, I guess it can't be changed there. Thanks.
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> ???
>> news:%23R4Ixpv0FHA.3068@TK2MSFTNGP10.phx.gbl ???...
>> > Can you explain what you mean by "change sql login username"? Are you
>> referring to the login name
>> > (in master) or the user name (in your database)? Anyhow, you cannot
> change
>> the name of a login or a
>> > user, You will be able to rename a user in 2005, not sure about login,
>> though.
>> >
>> > -- 
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> > Blog: http://solidqualitylearning.com/blogs/tibor/
>> >
>> >
>> > "Pleo" <rx8@hotmail.com> wrote in message
>> news:eVAlmnv0FHA.2428@tk2msftngp13.phx.gbl...
>> > > As title, thanks!
>> > >
>> > >
>> >
>>
>>
>
> 


Relevant Pages

  • Re: Logging in irrespective of database access
    ... Sysadmin role members have full permissions, ... SQL Server MVP ... > My problem is that in the Login section of Enterprise Manger I have to> specify that each login has the System Admin ticked under Server Roles> because in SQL 2000 there doesn't seem to a Grant All permissions option> like there used to be in SQL 6.5. ... > What's the point in having the Database Access section if the System Admin> under Server Roles allows you to get into anything?. ...
    (microsoft.public.sqlserver.server)
  • Re: Logging in irrespective of database access
    ... Sysadmin role members have full permissions, ... SQL Server MVP ... > My problem is that in the Login section of Enterprise Manger I have to> specify that each login has the System Admin ticked under Server Roles> because in SQL 2000 there doesn't seem to a Grant All permissions option> like there used to be in SQL 6.5. ... > What's the point in having the Database Access section if the System Admin> under Server Roles allows you to get into anything?. ...
    (microsoft.public.sqlserver.programming)
  • Re: Logging in irrespective of database access
    ... Sysadmin role members have full permissions, ... SQL Server MVP ... > My problem is that in the Login section of Enterprise Manger I have to> specify that each login has the System Admin ticked under Server Roles> because in SQL 2000 there doesn't seem to a Grant All permissions option> like there used to be in SQL 6.5. ... > What's the point in having the Database Access section if the System Admin> under Server Roles allows you to get into anything?. ...
    (microsoft.public.sqlserver.programming)
  • Re: Logging in irrespective of database access
    ... Sysadmin role members have full permissions, ... SQL Server MVP ... > My problem is that in the Login section of Enterprise Manger I have to> specify that each login has the System Admin ticked under Server Roles> because in SQL 2000 there doesn't seem to a Grant All permissions option> like there used to be in SQL 6.5. ... > What's the point in having the Database Access section if the System Admin> under Server Roles allows you to get into anything?. ...
    (microsoft.public.sqlserver.server)
  • Re: ODBC in MS Access ignores DSN authorization method
    ... login doesn't have permissions, you get an error. ... to use a trusted connection or a SQL login, ... >permissions, and instead use the PUBLIC permissions. ... >When the Windows login I am using has access to the database, ...
    (microsoft.public.sqlserver.odbc)