Re: How to set permissions for objects quickly
From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 10/17/05
- Next message: Pipo: "Re: sa loginname being hacked"
- Previous message: Dan Guzman: "Re: sa loginname being hacked"
- In reply to: Pleo: "How to set permissions for objects quickly"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
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> ¦b¶l¥ó 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! >> > > >> > > >> > >> >> > >
- Next message: Pipo: "Re: sa loginname being hacked"
- Previous message: Dan Guzman: "Re: sa loginname being hacked"
- In reply to: Pleo: "How to set permissions for objects quickly"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
