SQL Server Performance Forum – Threads Archive

Generate Grant script

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

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.

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.

yes satya.. It is on SQL Server 2005.. Thanks,
sonny

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.

is this way suppose to write suggestions?

?? 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.

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

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. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of 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.</center>

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.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by sonnysingh is this way suppose to write suggestions? <hr height="1" noshade id="quote"></blockquote id="quote"> When I saw this first, I was like ‘YE KYA BAAT KAR RAHA HAI’ [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] <pre id="code"> Name ——— Dilli Grg (1 row(s) affected) </pre id="code">

Dilli says ‘What he is talking?' [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Hum ko laga Paaji (Sonny) ko gussa aaya mere baat mein… I though Sonny got annoyed with my reply… Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of 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.</center>

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by satya Dilli says ‘What he is talking?' [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />][<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] <pre id="code"> Name ——— Dilli Grg (1 row(s) affected) </pre id="code">

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…

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.

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.

Let’s hope original poster doesn’t get confused with GRANT, DENY and REVOKE …

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

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.

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

http://vyaskn.tripod.com/scripting_permissions_in_sql_server_2005.htm &http://sqlserver-qa.net/blogs/perft…s-on-master-and-associated-user-database.aspx fyi. 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.

]]>