October 3, 2004 at 1:23 pm
Hi all,
Is there a way when you create a table to have it automatically assign permissions..?
Currently I create a table and then have to set the permissions for the public group to have select access.
Thanks
Rolf
October 4, 2004 at 3:57 am
Hi Rolf,
I'm not quite sure what you mean by "automatically".
However, on the off chance that you are looking for the SQL approach (as opposed to using the Enterprise Manager GUI approach), the following might be useful for you.
Using your own defined roles is also a good idea.
CREATE TABLE [dbo].[myTable]
(
[id] [int] IDENTITY (1,1) NOT NULL PRIMARY KEY,
[num] [int] NOT NULL
) ON [PRIMARY]
GO
GRANT EXEC ON dbo.myTable TO myDefinedRole
GO
Hope this is of help
Mark
October 4, 2004 at 4:01 am
Very sorrry,
please replace EXEC above with SELECT
(I always access data via SPs so I'm configured to type GRANT EXEC automatically)
October 4, 2004 at 4:27 am
It sounds like what you want to do is to have a template of security permissions that will be automatically applied to an object when it is created.
This is not possible with SQL2000. The strategic way of doing this in the Windows environment is by using a Group Policy Object (GPO). I would expect that Microsoft will extend GPO support to SQL Server objects eventually, but we will have to wait until SQL2005 at the earliest for this. Even then, it will probably only work on W2003 and XP.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 4, 2004 at 7:21 am
Not in SQL Server 2000 unless you are speaking of the db_datareader and db_datawriter fixed database roles.
Based on the PASS briefing, you will be able to assign security at any assembly level in SQL Server 2005. That means you could grant SELECT rights against a schema or a database and those would therefore apply to all tables and views. The example given in the briefing was EXECUTE rights to create an executor role for stored procedures similar to db_datareader and db_datawriter for tables and views. This will be internal to SQL Server, so it won't require any use of GPOs.
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply