-- create the app role
exec sp_addapprole 'MyAppRole', ''approlepassword'
-- grant it ALL priv
grant all to MyAppRole
-- create new new user
exec sp_addlogin 'User1', 'password', 'MyDatabase'
use MyDatabase
sp_grantdbaccess 'User1'
-- new user login and executes
exec sp_setapprole 'MyAppRole', ''approlepassword'
now User1 tries to do anyithing and they have no access to any objects, why
is this, I have granted all priv to the app role. I must be missing
something basic here.
Can anyone point me in the right direction.
Thanks,
Tony"Tony" <tonyng2@.spacecommand.net> wrote in message
news:exnIzfn6DHA.2568@.TK2MSFTNGP10.phx.gbl...
quote:
> I an having problems setting up an application role:
> -- create the app role
> exec sp_addapprole 'MyAppRole', ''approlepassword'
> -- grant it ALL priv
> grant all to MyAppRole
> -- create new new user
> exec sp_addlogin 'User1', 'password', 'MyDatabase'
> use MyDatabase
> sp_grantdbaccess 'User1'
> -- new user login and executes
> exec sp_setapprole 'MyAppRole', ''approlepassword'
> now User1 tries to do anyithing and they have no access to any objects,
why
quote:
> is this, I have granted all priv to the app role. I must be missing
> something basic here.
> Can anyone point me in the right direction.
> Thanks,
> Tony
>
>
GRANT ALL does not grant object permissions (SELECT, UPDATE etc.) - it
grants statement permissions (CREATE TABLE, BACKUP LOG etc.). To grant
object permissions, you need to grant individual permissions for each
object:
grant execute on proc1 to MyAppRole
grant update on table1 to MyAppRole
etc.
You can make this easier by using built-in roles like
db_datareader/db_datawriter, or by cutting, pasting, reviewing and executing
the output of a query like this:
select 'grant execute on ' + routine_name + ' to MyAppRole'
from information_schema.routines
where routine_type = 'procedure'
Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:401ff029$1_2@.news.bluewin.ch...
quote:
> "Tony" <tonyng2@.spacecommand.net> wrote in message
> news:exnIzfn6DHA.2568@.TK2MSFTNGP10.phx.gbl...
> why
> GRANT ALL does not grant object permissions (SELECT, UPDATE etc.) - it
> grants statement permissions (CREATE TABLE, BACKUP LOG etc.). To grant
> object permissions, you need to grant individual permissions for each
> object:
> grant execute on proc1 to MyAppRole
> grant update on table1 to MyAppRole
> etc.
> You can make this easier by using built-in roles like
> db_datareader/db_datawriter, or by cutting, pasting, reviewing and
executing
quote:
> the output of a query like this:
> select 'grant execute on ' + routine_name + ' to MyAppRole'
> from information_schema.routines
> where routine_type = 'procedure'
> Simon
>
--
But, I dynamically add/remove objects all the time. I surly don't want to
have to reset ll the permissions each time.
I guess using application roles is not going to cut if for me, since this
would be way to much of a maint. headache. Guess I will just have to assign
the users to built in roles unless someone knows an easier way to maintain
the app role when new objects are being added/removed at any time without
requiring the app role to be changed.
Would be really nice if I could grant the app role as another role such as
dbadmin.
Thanks,
Tony|||Okay figure out I can assign app role to be a member of db_owner.
But, when a user is set to the app role, all objects created by this user
get the app role owner, and not dbo as they should if they have db_owner.
Why is this?
Example:
sp_addlogin User1, password, db1
use db1
sp_grantdbaccess User1
sp_addapprole MyAppRole, rolepassword
sp_addrolemember db_owner, MyAppRole
Now user logins in as User1
sp_setapprole MyAppRole, rolepassword
Create table Test(Name varchar(50))
Test table is now owned by MyAppRole as MyAppRole.Test instead of dbo.Test
as is should (well I think it should)
Why is this?
Tony
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:401ff029$1_2@.news.bluewin.ch...
quote:
> "Tony" <tonyng2@.spacecommand.net> wrote in message
> news:exnIzfn6DHA.2568@.TK2MSFTNGP10.phx.gbl...
> why
> GRANT ALL does not grant object permissions (SELECT, UPDATE etc.) - it
> grants statement permissions (CREATE TABLE, BACKUP LOG etc.). To grant
> object permissions, you need to grant individual permissions for each
> object:
> grant execute on proc1 to MyAppRole
> grant update on table1 to MyAppRole
> etc.
> You can make this easier by using built-in roles like
> db_datareader/db_datawriter, or by cutting, pasting, reviewing and
executing
quote:|||A db_owner role member needs to explicitly specify owner 'dbo' in order to
> the output of a query like this:
> select 'grant execute on ' + routine_name + ' to MyAppRole'
> from information_schema.routines
> where routine_type = 'procedure'
> Simon
>
create dbo-owned objects.
CREATE TABLE dbo.Test(Name varchar(50))
Hope this helps.
Dan Guzman
SQL Server MVP
"Tony" <tonyng2@.spacecommand.net> wrote in message
news:%233fORlv6DHA.2572@.TK2MSFTNGP09.phx.gbl...
quote:|||Hmm, I 'm used to it doing that automatically if you ARE a db_owner.
> Okay figure out I can assign app role to be a member of db_owner.
> But, when a user is set to the app role, all objects created by this user
> get the app role owner, and not dbo as they should if they have db_owner.
> Why is this?
> Example:
> sp_addlogin User1, password, db1
> use db1
> sp_grantdbaccess User1
> sp_addapprole MyAppRole, rolepassword
> sp_addrolemember db_owner, MyAppRole
> Now user logins in as User1
> sp_setapprole MyAppRole, rolepassword
> Create table Test(Name varchar(50))
> Test table is now owned by MyAppRole as MyAppRole.Test instead of dbo.Test
> as is should (well I think it should)
> Why is this?
> Tony
> "Simon Hayes" <sql@.hayes.ch> wrote in message
> news:401ff029$1_2@.news.bluewin.ch...
objects,[QUOTE]
> executing
>
But I can handle that.
Thanks,
Tony
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23pkSCPy6DHA.2432@.TK2MSFTNGP10.phx.gbl...
quote:
> A db_owner role member needs to explicitly specify owner 'dbo' in order to
> create dbo-owned objects.
> CREATE TABLE dbo.Test(Name varchar(50))
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tony" <tonyng2@.spacecommand.net> wrote in message
> news:%233fORlv6DHA.2572@.TK2MSFTNGP09.phx.gbl...
user[QUOTE]
db_owner.[QUOTE]
dbo.Test[QUOTE]
> objects,
>
No comments:
Post a Comment