Click here to Skip to main content
15,030,701 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?
regards.

What I have tried:

1)
use testDB
go
grant alter to username

2)
use testDB
go
grant alter on database::testDB to username
Posted
Updated 8-Sep-16 19:08pm
v3
Comments
[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
SQL
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.

while
SQL
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.
   
Comments
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)[^]
   
Comments
Pouria Polouk 9-Sep-16 14:18pm
   
thank you
Karthik_Mahalingam 9-Sep-16 23:08pm
   
Welcome

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