GRANT permission on schema to user in SQL 2000 RRS feed

  • Question

  • Hi,

    I am using following statement to grant specified permissions on schema to a user in SQL Server 2005.
    GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE on ::[Schema Name] to [My DB User]

    I need to perform the same in SQL 2000. However, this does not work in SQL Server 2000. Is there any equivalent syntax which can be used in both (SQL 2000 & 2005).

    Thanks in advance...
    Monday, February 15, 2010 1:14 PM

All replies

  • Could you just place the permissions into separate statements?

    GRANT SELECT ON ::[Schema Name] TO [My DB User]
    GRANT INSERT ON ::[Schema Name] TO [My DB User]

    I dont have an edition of SQL 2000 so can't test it sorry.  Let me know if it works.



    Friday, September 17, 2010 2:04 AM
  • SQL 2000 only accepts


    Grant [insert,select,delete or etc] on object_name to user_name


    SQL 2005 accepts both


    Grant [insert,select,delete or etc] on object_name to user_name

    Grant [insert,select,delete or etc] on schema_name to user_name


    So you have to stick on to writing a script with grant permissions on object level rather than schema level.

    Thanks, Leks
    Friday, September 17, 2010 2:39 AM
  • I had the same problem in SQL Server 2000.  I don't know the T-SQL, but when I added the user to the 'db_datareader' role for the specific database I want them to have access to the schema of, I was then able to run select statements against INFORMATION_SCHEMA.
    • Edited by Baodad Friday, February 22, 2013 8:17 PM edit
    Friday, February 22, 2013 8:17 PM
  • Looks like you will have to go manually.  The script will generate the script you need to run.  Copy the output and paste into a query window.

    SELECT 'GRANT INSERT,SELECT ON ' + QUOTENAME(SCHEMA_NAME(uid)) + '.' + QUOTENAME(name) + ' to [userName]' 
    FROM sysobjects 
    WHERE SCHEMA_NAME(uid) = 'schemaName'

    Friday, February 22, 2013 11:27 PM