permissions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

permissions

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2006-03-15 : 03:36:40

some of you may recognize this, i think i got it from tara:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
USE Generis
GO
--The USE statement might fail. The GO statement clears out @@error. So you have to check DB_NAME if you don't want to run the risk of creating SProcs in the wrong DB.
IF DB_NAME()<>'Generis' BEGIN
RAISERROR('Wrong database.',16,10)
RETURN
END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_Grant_Permissions]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_Grant_Permissions]
GO
CREATE PROC isp_Grant_Permissions
----------------------------------------------------------------------------------------------------
-- OBJECT NAME : isp_Grant_Permissions
--
-- AUTHOR :
-- DATE : June 7, 2004
--
-- INPUTS : None
-- OUTPUTS : None
--
-- DEPENDENCIES : None
--
-- APPLICATION(s) :
--
-- DESCRIPTION :
--
-- EXAMPLES (optional) : EXEC isp_Grant_Permissions
--
-- MODIFICATION HISTORY :
----------------------------------------------------------------------------------------------------
-- MM/DD/YYYY - (Name)
-- (Description)
----------------------------------------------------------------------------------------------------
AS

SET NOCOUNT ON

DECLARE @objName varchar(80)
DECLARE @objType char(2)
DECLARE grant_perms_on_sps CURSOR FOR
SELECT name, type
FROM SYSOBJECTS
WHERE (
(type = 'P' )
OR
(type = 'FN' )
OR
(type = 'TF' )
OR
(type = 'U')
OR
(type = 'V' )
)
AND
uid = 1
AND
status > -1

OPEN grant_perms_on_sps
FETCH NEXT FROM grant_perms_on_sps
INTO @objName, @objType

WHILE @@FETCH_STATUS = 0
BEGIN
IF @objType IN ('FN', 'P')
BEGIN
EXEC ('GRANT EXECUTE ON dbo.' + @objName + ' TO lgnMyLogin')
END

ELSE
BEGIN
EXEC ('GRANT SELECT ON dbo.' + @objName + ' TO lgnMyLogin')
END

FETCH NEXT FROM grant_perms_on_sps
INTO @objName, @objType
END

CLOSE grant_perms_on_sps
DEALLOCATE grant_perms_on_sps

GO
exec isp_Grant_Permissions
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

it works fine on my 2005 server, but on my 2000 server it never completes
any ideas?
obviously this approach still works:

select 'grant execute on ' + name + ' to lgnMyLogin' from sysobjects where
type in ('FN') order by name

(and then do the same for tables, SProcs...)

but I'd rather stick with isp_Grant_Permissions because it's much better!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-15 : 03:41:47

what do you mean it never completes ?

----------------------------------
'KH'

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2006-03-15 : 04:11:31

execution never completes. it just keeps running. but on the 2005 server it completes within seconds for the same database.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-15 : 13:57:34

Yep that's from me. I haven't had any problems with it on SQL Server 2000. Perhaps someone is blocking you when you are running it.

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources