Objects access granted to an App Role

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Objects access granted to an App Role

Author

Topic

nicole
Yak Posting Veteran

97 Posts

Posted - 2006-07-31 : 03:05:49

I created an app role and granted access on specific tables, views, and stored procedures. How can I get a list of objects that the role has access?

schuhtl
Posting Yak Master

102 Posts

Posted - 2006-07-31 : 16:26:59

sp_helprotect @username = 'AppRoleNameGoesHere'

nicole
Yak Posting Veteran

97 Posts

Posted - 2006-07-31 : 21:38:58

Thanks schuhtl, I got it now!

Never think of the function will be "sp_helprotect" as I focus on the keywords like "role", "object", "access"

nicole
Yak Posting Veteran

97 Posts

Posted - 2006-07-31 : 22:08:44

After executing sp_helprotect, I got result like this:

Owner   Object	 Grantee    ProtectType	 Action	  Column
dbo	Office	 app_role   Grant	 Delete	  .
dbo	Office	 app_role   Grant	 Insert	  .
dbo	Office	 app_role   Grant	 Select	  (All+New)
dbo	Office	 app_role   Grant	 Update	  (All+New)
dbo	Port	 app_role   Grant	 Delete	  .
dbo	Port	 app_role   Grant	 Insert	  .
dbo	Port	 app_role   Grant	 Select	  (All+New)
dbo	Port	 app_role   Grant	 Update	  (All+New)

The above shows one action of each object per row. How can I have a consolidated result? For example,

Owner   Object	 Grantee    ProtectType	 Action
dbo	Office	 app_role   Grant	 Delete, Insert, Select, Update
dbo	Port	 app_role   Grant	 Delete, Insert, Select, Update

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-31 : 22:14:00

create a table with structure similar to sp_helpprotect result


create table yoru_table_name
(
    Owner    varchar(100),
    . .  .
)

insert into your_table_name exec sp_helprotect @username = 'AppRoleNameGoesHere'

use the string concat method here http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx to produce the result that you want

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-31 : 23:02:40

check what sp_helprotect is doing and extract the code you need

--------------------
keeping it simple...

nicole
Yak Posting Veteran

97 Posts

Posted - 2006-08-01 : 23:55:18

thanks all for the advise

humm.... sp_helprotect inserts records into a temp table after executing many insert/update statements, and returns specific columns selected from the temp table.

I think it's much more easier if using khtan's approach

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources