Click here to Skip to main content
15,849,262 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi all.
I'm using SQL Server 2014.
I've written 2 codes for grant permission.
Please see codes below.

what's difference between them?

What I have tried:

use testDB
grant alter to username

use testDB
grant alter on database::testDB to username
Updated 8-Sep-16 20:08pm
[no name] 8-Sep-16 11:02am    
What do you mean "what is the difference"? One will work and the other will not?
Pouria Polouk 8-Sep-16 11:52am    
Both of them work.
And both grant permission to the all objects on database level.
I just don’t know the difference.
[no name] 8-Sep-16 12:34pm    
Why not? Just look at them and you will see the difference. The second you wrote unnecessary commands.
Pouria Polouk 8-Sep-16 12:45pm    
Sorry, I'm not convinced completely.
What do you mean by "unnecessary"?
So why does Microsoft use both of them?

In the second statement you explicitly define the class (database) for which you grant privileges. Consider an example where you have and object called Test in a database named Test. Without defining the class it could be unclear for which class you are about to give permissions.

It's perhaps easiest to understand by looking at the error messages for invalid GRANT statement
The following statement
GRANT ALTER ON NotExisting TO SomeOne

would produce
Msg 15151, Level 16, State 1, Line 4
Cannot find the object 'NotExisting', because it does not exist or you do not have permission.

GRANT ALTER ON database::NotExisting TO SomeOne

would output
Msg 15151, Level 16, State 1, Line 6
Cannot find the database 'NotExisting', because it does not exist or you do not have permission.

In your first example you don't define any securable so database is used by default.
Share this answer
Pouria Polouk 8-Sep-16 12:57pm    
Thank you for your attention.
But I don't think you get my question!
Both does the same operation

GRANT ALTER TO USER is giving alter permission on all objects to the particular user

GRANT ALTER ON used to give permission on particular schema , refer GRANT Schema Permissions (Transact-SQL)[^]
Share this answer
Pouria Polouk 9-Sep-16 14:18pm    
thank you
Karthik_Mahalingam 9-Sep-16 23:08pm    

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900