Can Roles/Permissions Be Specified in Create? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Can Roles/Permissions Be Specified in Create?

Author

Topic

BobRoberts
Posting Yak Master

109 Posts

Posted - 2010-02-11 : 12:11:13

This is for SQL 2005/2008 Express: I notice that when I run the two scripts below, one to create a table, and the other to create a view of the table, the table and view it creates don't have any "Users or roles" when you click the Properties - Permissions. Because this table and view are used to insert, delete, update, and select rows, it needs to have a user/role of Public, and permissions for grantor to grant insert, delete update, and select. Is it possible to do this within the SQL script itself - or does that have to be done manually?
BEGIN

CREATE TABLE [dbo].[TestTable](
[SequenceNumber] [int] NOT NULL,
[EarningCode] [dbo].[pCode] NOT NULL,
[PrintSeqNo] [smallint] NULL,
[PrintCounter] [int] NOT NULL,
[EmployeeID] [dbo].[pEmpID] NULL
) ON [PRIMARY]

END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[TestTable]
AS
SELECT SequenceNumber, EarningCode, PrintSeqNo, PrintCounter, EmployeeID
FROM dbo.TestTable

GO

Kristen
Test

22859 Posts

Posted - 2010-02-11 : 12:31:00

You can "script" it, and thus include it in your script, but you can't put it in the CREATE statement itself (AFAIK)


GRANT SELECT, INSERT, UPDATE, DELETE
      ON [dbo].[TestTable]
      TO SomeUserOrRoleName

BobRoberts
Posting Yak Master

109 Posts

Posted - 2010-02-11 : 14:46:50

Yes, that's exactly what I needed - thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources