SQL Server Performance

Generate Grant script

Discussion in 'SQL Server 2005 General DBA Questions' started by sonnysingh, Jun 8, 2007.

  1. sonnysingh Member

    Hi Folks

    I want to generte a script of GRANT ALL or for all possible permissions as I do not want to give db_owner rights to the user. The permissions will be on database level.
    Permissions are: CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE,CREATE VIEW and similiarly ALTER, INSERT, DELETE, UPDATE, SELECT, EXECUTE.

    Thanks in Advance
    sonny

  2. MohammedU New Member

    You can use...
    db_datareader, db_datawriter and db_ddladmin...
    and
    SELECT 'GRANT ALL ON '+name+' TO <USERNAME>' FROM SYSOBJECTS WHERE
    objectproperty (id, 'ismsshipped') = 0
    Take the output of above statement and execute it....


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  3. satya Moderator

    Is it on SQL 2005?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  4. sonnysingh Member

    yes satya.. It is on SQL Server 2005..

    Thanks,
    sonny
  5. satya Moderator

    Try with Mohammed's script or even use generate script wizard in SSMS>

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  6. sonnysingh Member

    is this way suppose to write suggestions?
  7. satya Moderator

    ?? I don't mean to offend you or simply write few words.
    Using Generate Script wizard in SSMS for SQL 2005 you can achieve to create such scripts, hence my reply to use both of the options.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  8. sonnysingh Member



    Satya... It wasn't for you at all and your answers never been offended. There was reply from user n/a and refer ofsome viagra sites. That's why I have added my answer that meant to be for user n/a rather you..

    Please do not get offended..

    sonny
  9. satya Moderator

    Great, thanks a bunch for the clarification [<img src='/community/emoticons/emotion-1.gif' alt=':)' />], I wasn't aware of that post as we (moderators) delete the SPAM posts straight away. So quoting such posts will help in future.<br /><br /><br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
  10. MohammedU New Member

    quote:Originally posted by sonnysingh



    Satya... It wasn't for you at all and your answers never been offended. There was reply from user n/a and refer ofsome viagra sites. That's why I have added my answer that meant to be for user n/a rather you..

    Please do not get offended..

    sonny

    sonny,

    This the internet world...and SPAM is part of the life in this Internet world...
    Just avoid them...they don't read your message... even they read also...don't care....

    Take it easy... and move on...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  11. DilliGrg Member

    <br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by sonnysingh</i><br /><br />is this way suppose to write suggestions?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />When I saw this first, I was like 'YE KYA BAAT KAR RAHA HAI' [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br /><br />Name<br />--------- <br />Dilli Grg <br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code">
  12. satya Moderator

    Dilli says 'What he is talking?'<br /><br />[<img src='/community/emoticons/emotion-2.gif' alt=':D' />] Hum ko laga Paaji (Sonny) ko gussa aaya mere baat mein...<br /><br />I though Sonny got annoyed with my reply...<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
  13. DilliGrg Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by satya</i><br /><br />Dilli says 'What he is talking?'<br /><br />[<img src='/community/emoticons/emotion-2.gif' alt=':D' />] Hum ko laga Paaji (Sonny) ko gussa aaya mere baat mein...<br /><br />I though Sonny got annoyed with my reply...<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />[<img src='/community/emoticons/emotion-2.gif' alt=':D' />][<img src='/community/emoticons/emotion-2.gif' alt=':D' />][<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br /><br />Name<br />--------- <br />Dilli Grg <br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code">
  14. sonnysingh Member

    Guys... Thanks for sense of humour.. I back to this thread cos I have related question to ask. guess i got surprise.. for me..

    Hum log ki Achhi kismat hai ke hum ko Achhe log Mile (we are really lucky to know good people here)
    . It is really good know you lot guys... Thanks.

    Question is I have given db_datareader and db_datawriter rights to the users with execution for SPs. But I need to take off only Delete Rights from these users. How?

    Thanks in Advance again...
  15. Adriaan New Member

    Deny delete permissions on the relevant tables.

    If you;re used to database platforms like Access, you can grant a privilege, or not grant it.

    In SQL Server, you can also deny a privilege. The denied permission overrules any permission granted or implied to the login for the same object.
  16. satya Moderator

    To addup Adrian's if you need a specific delete permission for set of tables then you take help of roles, as of now you have no other option to REVOKE delete permission on the database.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  17. Adriaan New Member

    Let's hope original poster doesn't get confused with GRANT, DENY and REVOKE ...
  18. sonnysingh Member


    This will be the command??

    DENY DELETE ON Call_Details to kumar

    What about generate script for all the tables and SPS OR for whole database?

    Thanks in Advance
  19. satya Moderator

    Yes, you have to perform a manual procedure to script out current privileges on the database for the users in order to identify relevant login to grant.

    Also is it on SQL 2005?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  20. sonnysingh Member

    yes it is on sql 2005.. I try to find command that run on the whole database rather generate script for individual objects(cos in this way you can possibly miss some of the objects)...but couldn't yet..

    help on it.

    Thanks in Advance
  21. satya Moderator

Share This Page