grant privilege

Question

grant privilege

newbieuser

SSCrazy Eights

Points: 9423
More actions
August 16, 2010 at 12:55 pm

#225954

Hi all,
I'm able to do this via management studio under securables tab in the application role by adding specific objects and by giving 'control' permission to the selected database. But will the 'control' permission give full access to the database including symmetric keys executing stored procedures etc?
I would like to know if is possible to script this grant permissions for full control of database to an application role. When a new user is created he will have to execute this role to access the database and this role should have 'full' control of the database.
Also, Is there a sql to see what permissions the application role has?
Thanks so much

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

You can script it, though I'm not sure what you're trying to do here. Why "Control" permission? This is typically a higher level of permissions than most users need.

The GRANT statement adds permissions like this:

GRANT ALL | keyword1 [, keyword2, …]

[ON table | view (column1, [column2, … ]) |

ON storedprocedure | extendedprocedure |

ON udf]

TO applicationrole

You can script something to fill in the stored procedures or columns and then add the application role in the TO clause.

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 2

Thanks. But, actually the application needs full control(alter,insert,delete,execute, select,view definition etc). of all the database objects (like tables,views,stored procedures,functions,triggers,symmetric keys etc).

And I get this for grant all 'The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity'..

When the application user executes the role, the role should have full access to the database.. Is there a command to do this? Is it grant control to database?

Also please someone tell me if there is a sql to show all the permissions assigned to a role?

Thanks again

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 3

You can use ALL, or CONTROL.

You can use this function to check permissions: http://technet.microsoft.com/en-us/library/ms176097.aspx

What application executes an application role and then needs to alter objects? I'm curious.

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 4

Thanks Steve.

I use exec sp_setapprole to adopt the role in a session. Just wanting to know if there is a command to unset the approle from a session or set another approle in the same session without exiting the session?

Thanks again

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 5

http://msdn.microsoft.com/en-us/library/ms365415.aspx

sp_unsetapprole

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 6

Thanks.

DECLARE @cookie varbinary(8000);

EXEC sp_setapprole 'test', 'password1'

, @fCreateCookie = true, @cookie = @cookie OUTPUT;

Command(s) completed successfully.

Then when I execute sp_unsetapprole in the same session, I get this error..

DECLARE @cookie varbinary(8000);

EXEC sp_unsetapprole @cookie;

Msg 290, Level 16, State 2, Procedure sp_unsetapprole, Line 22

Invalid EXECUTE statement using object "AppRole", method "Unset".

Thanks for your help