locked
using Grant in mssql RRS feed

  • Question

  • hi there,

    how can i execute the following in mssql:

    GRANT SELECT, UPDATE, INSERT, DELETE, DROP, CREATE, ALTER 
    ON sales_test.* 
    TO sales_test_admin
    
    GRANT SELECT, UPDATE, INSERT, DELETE 
    ON sales_test.* 
    TO sales_test_user

    where sales test is the db and  sales_test_admin and sales_test_user are users

    Saturday, October 6, 2012 4:27 PM

Answers

  • GRANT SELECT, UPDATE, INSERT, DELETE, DROP, CREATE, ALTER
    ON DATABASE::sales_test
     TO sales_test_admin

    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by mostafaxxx Saturday, October 6, 2012 11:04 PM
    Saturday, October 6, 2012 5:56 PM

All replies

  • Is sales_test a schema?

    In such case:

    GRANT SELECT, UPDATE, INSERT, DELETE, DROP, CREATE, ALTER
    ON SCHEMA::sales_test
    TO sales_test_admin


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, October 6, 2012 4:53 PM
  • sales_test is just a database.
    Saturday, October 6, 2012 5:15 PM
  • GRANT SELECT, UPDATE, INSERT, DELETE, DROP, CREATE, ALTER
    ON DATABASE::sales_test
     TO sales_test_admin

    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by mostafaxxx Saturday, October 6, 2012 11:04 PM
    Saturday, October 6, 2012 5:56 PM
  • Although there are no DROP or CREATE permissions in SQL Server. You can have CREATE TABLE, CREATE PROCEDURE etc permission. The simplest, though, may be add sales_test_admin as member in the predefined database role db_ddladmin.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, October 6, 2012 6:35 PM
  • getting error user sales_test_user doesn't exists
    Saturday, October 6, 2012 7:35 PM
  • Obviously you need to create sales_test_user first. Alternatively, you need to be in the right database.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, October 6, 2012 7:46 PM
  • i have a user named sales_test_user

    it's under the main security node in the logins

    Saturday, October 6, 2012 7:59 PM
  • sorry guys i don't mean to be a BETA

    what is the different between the user at the logins and those in the database users ??

    • Edited by mostafaxxx Saturday, October 6, 2012 8:18 PM
    Saturday, October 6, 2012 8:12 PM
  • To use SQL Server parlance, on server level you have logins, on database level you have users. A database user normally maps to a server login. And normally the name on server and database level is the same, but the mapping is by SID and names can be different (but only do this if you like to confuse).

    The idea is that there may be many databases on the server, but user A should only be able to access db1, db16 and db89. Therefore he is only granted access to these three databases. To access to the server at all, he needs a server login.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, October 6, 2012 9:09 PM
  • As you learn more about the permissions, check this poster http://go.microsoft.com/fwlink/?LinkId=229142 

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, October 8, 2012 4:06 PM