Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    May 2004
    Posts
    44

    Unanswered: Problems on Create proc includes granting create table or view perissinin SP

    Hi All,

    I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:

    Create PROC dbo.GrantPermission
    @user1 varchar(50)

    as

    Grant create table to @user1
    go

    Grant create view to @user1
    go

    Grant create Procedure to @user1
    Go



    Thanks Guys.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can't have "GO" inside a stored procedure, and GRANT doesn't take variables as parameters. That being said, you have to resort to being a sneaky bugger and come up with something like:
    Code:
    CREATE PROC dbo.GrantPermission
       @user1 varchar(50)
    AS
    
    EXECUTE ('GRANT CREATE TABLE TO [' + @user1 + ']')
    EXECUTE ('GRANT CREATE VIEW TO [' + @user1 + ']')
    EXECUTE ('Grant create Procedure to [' + @user1 + ']')
    
    RETURN
    GO
    -PatP

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    With Apologies to the SQL Warrior Princess



    Code:
    CREATE PROC isp_Grant_Permissions
    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' AND name LIKE 'usp[_]%') 
    		OR 
    		(type = 'FN' AND name like 'udf[_]%')
    		OR
    		(type = 'TF' AND name like 'udf[_]%')
    		OR
    		(type = 'U')
    		OR
    		(type = 'V' AND name like '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 NOT IN ('TF', 'T', 'V')
    	BEGIN
    		EXEC ('GRANT EXECUTE ON dbo.' + @objName + ' TO GT_Apps')
    	END
    
    	ELSE
    	BEGIN
    		EXEC ('GRANT SELECT ON dbo.' + @objName + ' TO GT_Apps')	
    	END
    
    	FETCH NEXT FROM grant_perms_on_sps 
    	INTO @objName, @objType
    END
    
    CLOSE grant_perms_on_sps
    DEALLOCATE grant_perms_on_sps
    
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Methinks thou art corn-fused. Tara's script deals with object permissions, and uses her naming conventions. The requested script deals with user permissions, and is rather ignorant of objects altogether. Did I miss another meeting somewhere ?!?!

    -PatP

  5. #5
    Join Date
    May 2004
    Posts
    44
    Thanks Pat. But I couldn't see anything wrong with the codes. Could you show me it?

  6. #6
    Join Date
    May 2004
    Posts
    44
    Pat, I mean your codes.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rosem
    Thanks Pat. But I couldn't see anything wrong with the codes. Could you show me it?
    I don't understand the question. What would you like me to show you?

    -PatP

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Pat Phelan
    Methinks thou art corn-fused. Tara's script deals with object permissions, and uses her naming conventions. The requested script deals with user permissions, and is rather ignorant of objects altogether. Did I miss another meeting somewhere ?!?!

    -PatP

    Holy sh-t...the brain's already on the road.....

    dooh
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    Holy sh-t...the brain's already on the road.....

    dooh
    Don't let it get too far! You don't want to let it getting used to going out to play without bringing you along to supervise!

    I just figured that I'd missed another one of those "executive circle" meetings or something! I was hoping that I wasn't in real trouble yet.

    -PatP

  10. #10
    Join Date
    May 2004
    Posts
    44
    Thanks Guys.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rosem
    Pat, I mean your codes.
    I'm sorry Rose, but I'm still lost. If you want to see the code that I wrote, it is in the post above (meant to be copied verbatim).

    Anywho, coach me a bit if that doesn't answer your question. I'll be happy to take another shot at it!

    -PatP

  12. #12
    Join Date
    May 2004
    Posts
    44
    Hi Pat,

    I copied your code to my SP, and tried to call it, but got error, it seems I put wrong variable, please advise me. This is I used:

    Exec GrantPermission @user1 = 'Tom'


    Thanks.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Please copy the error message from Query Analyzer and paste it into a message. I just ran the procedure on my machine, and it works fine if I use:
    Code:
    EXECUTE dbo.GrantPermission @user1 = 'dbo'
    -PatP

  14. #14
    Join Date
    May 2004
    Posts
    44
    Pat, this is I got:

    EXECUTE dbo.GrantPermission @user1 = 'dbo'

    Server: Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'Procedure'.

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Are you sure you've highlighted the code before you executed it?

    You are using query analyzer right?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •