Q: A standard database role named abc is created and assigned permissions to
db_reader and db_writer roles.
Then the database users are made members to the role abc.
(The DatabaseAccess under users displays viaGroupMembership.)
But why doesnot users have access to the database?
(SQLServer2000)Hi,
Please let me now what error message, if any, you receive when you attempt
to access the database. Also, if you have auditing enabled, what error
message, if any, do you receive in the SQL Error Log or the Application
Event Log?
Based on my test, the configuration you describe should work OK.
Thanks.
Gary
This posting is provided "AS IS" with no warranties, and confers no rights.
Showing posts with label assigned. Show all posts
Showing posts with label assigned. Show all posts
Friday, March 23, 2012
permissions resetting on a View
I have a View that pulls data from one table.
I have assigned a Role with Select-only permission for
that View.
For some reason the permissions for that Role/View keep
getting deleted, so I have to go back and re-grant Select
access over and over again.
Anybody know why this is happening, and is there any way
to prevent it? There should be no change to the
permissions for that Role at all.
TIA,
TerrellWhen you delete a Role or a View, or any other object for that matter, you
also delete the permissions associated with those objects. If you what to
keep the permissions for the View then don't delete the view and recreate,
but instead just ALTER the view. When you ALTER an object the permissions
that are associated with the object stay intact.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Terrell Miller" <millerto@.bellsouth.net> wrote in message
news:2afa201c46819$3b6e4510$a501280a@.phx
.gbl...
> I have a View that pulls data from one table.
> I have assigned a Role with Select-only permission for
> that View.
> For some reason the permissions for that Role/View keep
> getting deleted, so I have to go back and re-grant Select
> access over and over again.
> Anybody know why this is happening, and is there any way
> to prevent it? There should be no change to the
> permissions for that Role at all.
> TIA,
> Terrell|||
>--Original Message--
>When you delete a Role or a View, or any other object for
that matter, you
>also delete the permissions associated with those
objects. If you what to
>keep the permissions for the View then don't delete the
view and recreate,
>but instead just ALTER the view. When you ALTER an
object the permissions
>that are associated with the object stay intact.
Greg, we aren't changing the view or the roles. It's just
that from time to time the Select permission on that View
gets removed.
Question: when you use sp_refreshview does that actually
delete the view and recreate it? I can't set up an ALTER
inside a sproc (because the ALTER has to be the first line
in a batch, but the CREATE PROCEDURE statement has to
execute before it), which is why I'm using sp_refreshview.
Thanks again,
Terrell|||Since the sp_renameview is a system store procedure, I'm not exactly sure
whether it drops and recreates the view. I did a little test I did, when
you run the sp_refreshview it appears to keep the permissions on a view.
If you really want to create or alter a view via a stored procedure you can
do that with dynamic SQL. Something like so:
create procedure yoursp as
declare @.cmd char(1000)
set @.cmd = 'alter view yourview as select bing, bang, boom from yourtable'
exec(@.cmd)
-- rest of sp
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Terrell Miller" <millerto@.bellsouth.net> wrote in message
news:2b1a001c4682f$259d8b90$a501280a@.phx
.gbl...
>
> that matter, you
> objects. If you what to
> view and recreate,
> object the permissions
> Greg, we aren't changing the view or the roles. It's just
> that from time to time the Select permission on that View
> gets removed.
> Question: when you use sp_refreshview does that actually
> delete the view and recreate it? I can't set up an ALTER
> inside a sproc (because the ALTER has to be the first line
> in a batch, but the CREATE PROCEDURE statement has to
> execute before it), which is why I'm using sp_refreshview.
> Thanks again,
> Terrell
I have assigned a Role with Select-only permission for
that View.
For some reason the permissions for that Role/View keep
getting deleted, so I have to go back and re-grant Select
access over and over again.
Anybody know why this is happening, and is there any way
to prevent it? There should be no change to the
permissions for that Role at all.
TIA,
TerrellWhen you delete a Role or a View, or any other object for that matter, you
also delete the permissions associated with those objects. If you what to
keep the permissions for the View then don't delete the view and recreate,
but instead just ALTER the view. When you ALTER an object the permissions
that are associated with the object stay intact.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Terrell Miller" <millerto@.bellsouth.net> wrote in message
news:2afa201c46819$3b6e4510$a501280a@.phx
.gbl...
> I have a View that pulls data from one table.
> I have assigned a Role with Select-only permission for
> that View.
> For some reason the permissions for that Role/View keep
> getting deleted, so I have to go back and re-grant Select
> access over and over again.
> Anybody know why this is happening, and is there any way
> to prevent it? There should be no change to the
> permissions for that Role at all.
> TIA,
> Terrell|||
>--Original Message--
>When you delete a Role or a View, or any other object for
that matter, you
>also delete the permissions associated with those
objects. If you what to
>keep the permissions for the View then don't delete the
view and recreate,
>but instead just ALTER the view. When you ALTER an
object the permissions
>that are associated with the object stay intact.
Greg, we aren't changing the view or the roles. It's just
that from time to time the Select permission on that View
gets removed.
Question: when you use sp_refreshview does that actually
delete the view and recreate it? I can't set up an ALTER
inside a sproc (because the ALTER has to be the first line
in a batch, but the CREATE PROCEDURE statement has to
execute before it), which is why I'm using sp_refreshview.
Thanks again,
Terrell|||Since the sp_renameview is a system store procedure, I'm not exactly sure
whether it drops and recreates the view. I did a little test I did, when
you run the sp_refreshview it appears to keep the permissions on a view.
If you really want to create or alter a view via a stored procedure you can
do that with dynamic SQL. Something like so:
create procedure yoursp as
declare @.cmd char(1000)
set @.cmd = 'alter view yourview as select bing, bang, boom from yourtable'
exec(@.cmd)
-- rest of sp
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Terrell Miller" <millerto@.bellsouth.net> wrote in message
news:2b1a001c4682f$259d8b90$a501280a@.phx
.gbl...
>
> that matter, you
> objects. If you what to
> view and recreate,
> object the permissions
> Greg, we aren't changing the view or the roles. It's just
> that from time to time the Select permission on that View
> gets removed.
> Question: when you use sp_refreshview does that actually
> delete the view and recreate it? I can't set up an ALTER
> inside a sproc (because the ALTER has to be the first line
> in a batch, but the CREATE PROCEDURE statement has to
> execute before it), which is why I'm using sp_refreshview.
> Thanks again,
> Terrell
permissions resetting on a View
I have a View that pulls data from one table.
I have assigned a Role with Select-only permission for
that View.
For some reason the permissions for that Role/View keep
getting deleted, so I have to go back and re-grant Select
access over and over again.
Anybody know why this is happening, and is there any way
to prevent it? There should be no change to the
permissions for that Role at all.
TIA,
Terrell
When you delete a Role or a View, or any other object for that matter, you
also delete the permissions associated with those objects. If you what to
keep the permissions for the View then don't delete the view and recreate,
but instead just ALTER the view. When you ALTER an object the permissions
that are associated with the object stay intact.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Terrell Miller" <millerto@.bellsouth.net> wrote in message
news:2afa201c46819$3b6e4510$a501280a@.phx.gbl...
> I have a View that pulls data from one table.
> I have assigned a Role with Select-only permission for
> that View.
> For some reason the permissions for that Role/View keep
> getting deleted, so I have to go back and re-grant Select
> access over and over again.
> Anybody know why this is happening, and is there any way
> to prevent it? There should be no change to the
> permissions for that Role at all.
> TIA,
> Terrell
|||
>--Original Message--
>When you delete a Role or a View, or any other object for
that matter, you
>also delete the permissions associated with those
objects. If you what to
>keep the permissions for the View then don't delete the
view and recreate,
>but instead just ALTER the view. When you ALTER an
object the permissions
>that are associated with the object stay intact.
Greg, we aren't changing the view or the roles. It's just
that from time to time the Select permission on that View
gets removed.
Question: when you use sp_refreshview does that actually
delete the view and recreate it? I can't set up an ALTER
inside a sproc (because the ALTER has to be the first line
in a batch, but the CREATE PROCEDURE statement has to
execute before it), which is why I'm using sp_refreshview.
Thanks again,
Terrell
|||Since the sp_renameview is a system store procedure, I'm not exactly sure
whether it drops and recreates the view. I did a little test I did, when
you run the sp_refreshview it appears to keep the permissions on a view.
If you really want to create or alter a view via a stored procedure you can
do that with dynamic SQL. Something like so:
create procedure yoursp as
declare @.cmd char(1000)
set @.cmd = 'alter view yourview as select bing, bang, boom from yourtable'
exec(@.cmd)
-- rest of sp
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Terrell Miller" <millerto@.bellsouth.net> wrote in message
news:2b1a001c4682f$259d8b90$a501280a@.phx.gbl...
> that matter, you
> objects. If you what to
> view and recreate,
> object the permissions
> Greg, we aren't changing the view or the roles. It's just
> that from time to time the Select permission on that View
> gets removed.
> Question: when you use sp_refreshview does that actually
> delete the view and recreate it? I can't set up an ALTER
> inside a sproc (because the ALTER has to be the first line
> in a batch, but the CREATE PROCEDURE statement has to
> execute before it), which is why I'm using sp_refreshview.
> Thanks again,
> Terrell
I have assigned a Role with Select-only permission for
that View.
For some reason the permissions for that Role/View keep
getting deleted, so I have to go back and re-grant Select
access over and over again.
Anybody know why this is happening, and is there any way
to prevent it? There should be no change to the
permissions for that Role at all.
TIA,
Terrell
When you delete a Role or a View, or any other object for that matter, you
also delete the permissions associated with those objects. If you what to
keep the permissions for the View then don't delete the view and recreate,
but instead just ALTER the view. When you ALTER an object the permissions
that are associated with the object stay intact.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Terrell Miller" <millerto@.bellsouth.net> wrote in message
news:2afa201c46819$3b6e4510$a501280a@.phx.gbl...
> I have a View that pulls data from one table.
> I have assigned a Role with Select-only permission for
> that View.
> For some reason the permissions for that Role/View keep
> getting deleted, so I have to go back and re-grant Select
> access over and over again.
> Anybody know why this is happening, and is there any way
> to prevent it? There should be no change to the
> permissions for that Role at all.
> TIA,
> Terrell
|||
>--Original Message--
>When you delete a Role or a View, or any other object for
that matter, you
>also delete the permissions associated with those
objects. If you what to
>keep the permissions for the View then don't delete the
view and recreate,
>but instead just ALTER the view. When you ALTER an
object the permissions
>that are associated with the object stay intact.
Greg, we aren't changing the view or the roles. It's just
that from time to time the Select permission on that View
gets removed.
Question: when you use sp_refreshview does that actually
delete the view and recreate it? I can't set up an ALTER
inside a sproc (because the ALTER has to be the first line
in a batch, but the CREATE PROCEDURE statement has to
execute before it), which is why I'm using sp_refreshview.
Thanks again,
Terrell
|||Since the sp_renameview is a system store procedure, I'm not exactly sure
whether it drops and recreates the view. I did a little test I did, when
you run the sp_refreshview it appears to keep the permissions on a view.
If you really want to create or alter a view via a stored procedure you can
do that with dynamic SQL. Something like so:
create procedure yoursp as
declare @.cmd char(1000)
set @.cmd = 'alter view yourview as select bing, bang, boom from yourtable'
exec(@.cmd)
-- rest of sp
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Terrell Miller" <millerto@.bellsouth.net> wrote in message
news:2b1a001c4682f$259d8b90$a501280a@.phx.gbl...
> that matter, you
> objects. If you what to
> view and recreate,
> object the permissions
> Greg, we aren't changing the view or the roles. It's just
> that from time to time the Select permission on that View
> gets removed.
> Question: when you use sp_refreshview does that actually
> delete the view and recreate it? I can't set up an ALTER
> inside a sproc (because the ALTER has to be the first line
> in a batch, but the CREATE PROCEDURE statement has to
> execute before it), which is why I'm using sp_refreshview.
> Thanks again,
> Terrell
permissions resetting on a View
I have a View that pulls data from one table.
I have assigned a Role with Select-only permission for
that View.
For some reason the permissions for that Role/View keep
getting deleted, so I have to go back and re-grant Select
access over and over again.
Anybody know why this is happening, and is there any way
to prevent it? There should be no change to the
permissions for that Role at all.
TIA,
TerrellWhen you delete a Role or a View, or any other object for that matter, you
also delete the permissions associated with those objects. If you what to
keep the permissions for the View then don't delete the view and recreate,
but instead just ALTER the view. When you ALTER an object the permissions
that are associated with the object stay intact.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Terrell Miller" <millerto@.bellsouth.net> wrote in message
news:2afa201c46819$3b6e4510$a501280a@.phx.gbl...
> I have a View that pulls data from one table.
> I have assigned a Role with Select-only permission for
> that View.
> For some reason the permissions for that Role/View keep
> getting deleted, so I have to go back and re-grant Select
> access over and over again.
> Anybody know why this is happening, and is there any way
> to prevent it? There should be no change to the
> permissions for that Role at all.
> TIA,
> Terrell|||>--Original Message--
>When you delete a Role or a View, or any other object for
that matter, you
>also delete the permissions associated with those
objects. If you what to
>keep the permissions for the View then don't delete the
view and recreate,
>but instead just ALTER the view. When you ALTER an
object the permissions
>that are associated with the object stay intact.
Greg, we aren't changing the view or the roles. It's just
that from time to time the Select permission on that View
gets removed.
Question: when you use sp_refreshview does that actually
delete the view and recreate it? I can't set up an ALTER
inside a sproc (because the ALTER has to be the first line
in a batch, but the CREATE PROCEDURE statement has to
execute before it), which is why I'm using sp_refreshview.
Thanks again,
Terrell|||Since the sp_renameview is a system store procedure, I'm not exactly sure
whether it drops and recreates the view. I did a little test I did, when
you run the sp_refreshview it appears to keep the permissions on a view.
If you really want to create or alter a view via a stored procedure you can
do that with dynamic SQL. Something like so:
create procedure yoursp as
declare @.cmd char(1000)
set @.cmd = 'alter view yourview as select bing, bang, boom from yourtable'
exec(@.cmd)
-- rest of sp
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Terrell Miller" <millerto@.bellsouth.net> wrote in message
news:2b1a001c4682f$259d8b90$a501280a@.phx.gbl...
> >--Original Message--
> >When you delete a Role or a View, or any other object for
> that matter, you
> >also delete the permissions associated with those
> objects. If you what to
> >keep the permissions for the View then don't delete the
> view and recreate,
> >but instead just ALTER the view. When you ALTER an
> object the permissions
> >that are associated with the object stay intact.
> Greg, we aren't changing the view or the roles. It's just
> that from time to time the Select permission on that View
> gets removed.
> Question: when you use sp_refreshview does that actually
> delete the view and recreate it? I can't set up an ALTER
> inside a sproc (because the ALTER has to be the first line
> in a batch, but the CREATE PROCEDURE statement has to
> execute before it), which is why I'm using sp_refreshview.
> Thanks again,
> Terrell
I have assigned a Role with Select-only permission for
that View.
For some reason the permissions for that Role/View keep
getting deleted, so I have to go back and re-grant Select
access over and over again.
Anybody know why this is happening, and is there any way
to prevent it? There should be no change to the
permissions for that Role at all.
TIA,
TerrellWhen you delete a Role or a View, or any other object for that matter, you
also delete the permissions associated with those objects. If you what to
keep the permissions for the View then don't delete the view and recreate,
but instead just ALTER the view. When you ALTER an object the permissions
that are associated with the object stay intact.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Terrell Miller" <millerto@.bellsouth.net> wrote in message
news:2afa201c46819$3b6e4510$a501280a@.phx.gbl...
> I have a View that pulls data from one table.
> I have assigned a Role with Select-only permission for
> that View.
> For some reason the permissions for that Role/View keep
> getting deleted, so I have to go back and re-grant Select
> access over and over again.
> Anybody know why this is happening, and is there any way
> to prevent it? There should be no change to the
> permissions for that Role at all.
> TIA,
> Terrell|||>--Original Message--
>When you delete a Role or a View, or any other object for
that matter, you
>also delete the permissions associated with those
objects. If you what to
>keep the permissions for the View then don't delete the
view and recreate,
>but instead just ALTER the view. When you ALTER an
object the permissions
>that are associated with the object stay intact.
Greg, we aren't changing the view or the roles. It's just
that from time to time the Select permission on that View
gets removed.
Question: when you use sp_refreshview does that actually
delete the view and recreate it? I can't set up an ALTER
inside a sproc (because the ALTER has to be the first line
in a batch, but the CREATE PROCEDURE statement has to
execute before it), which is why I'm using sp_refreshview.
Thanks again,
Terrell|||Since the sp_renameview is a system store procedure, I'm not exactly sure
whether it drops and recreates the view. I did a little test I did, when
you run the sp_refreshview it appears to keep the permissions on a view.
If you really want to create or alter a view via a stored procedure you can
do that with dynamic SQL. Something like so:
create procedure yoursp as
declare @.cmd char(1000)
set @.cmd = 'alter view yourview as select bing, bang, boom from yourtable'
exec(@.cmd)
-- rest of sp
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Terrell Miller" <millerto@.bellsouth.net> wrote in message
news:2b1a001c4682f$259d8b90$a501280a@.phx.gbl...
> >--Original Message--
> >When you delete a Role or a View, or any other object for
> that matter, you
> >also delete the permissions associated with those
> objects. If you what to
> >keep the permissions for the View then don't delete the
> view and recreate,
> >but instead just ALTER the view. When you ALTER an
> object the permissions
> >that are associated with the object stay intact.
> Greg, we aren't changing the view or the roles. It's just
> that from time to time the Select permission on that View
> gets removed.
> Question: when you use sp_refreshview does that actually
> delete the view and recreate it? I can't set up an ALTER
> inside a sproc (because the ALTER has to be the first line
> in a batch, but the CREATE PROCEDURE statement has to
> execute before it), which is why I'm using sp_refreshview.
> Thanks again,
> Terrell
Saturday, February 25, 2012
Permission Issues
Dear all,
I created a login name xxx and assigned few permisson on database <xyz>.
Now i want to grant execute permission to user xxx on extended stored procedure xp_cmdshell.
Without creating user in master database is there other way to grant the same to user xxx.
Regards
Mohd Sufian
Using xp_cmdshell requires a high level of permissions.
You can create a proxy account for xp_cmdshell, put the use of xp_cmdshell into a stored procedure, and then give your user permission for the stored procedure.
Check in Books Online about xp_cmdshell and proxy accounts.
Subscribe to:
Posts (Atom)