March 30, 2011 at 8:04 am
I may be a little long winded on my explanation becuase i'm somewhat a newbie when it comes to sql server security.
I have two differenct permissions.
webUser - which is the user who should have select permissions to the new table
billingUser - this is the user who has db_owner.
BillingUser runs a monthly process to archive data and then create a new table for the current month. In the past when he did this the user webUser automatically got select permissions to this new table. However since we migrated the db to another box this no longer happens and i have to do it manually. Does anyone have any idea how i set this up to ensure the select permissions are available to webUser when BillingUser creates a new table?
March 30, 2011 at 9:56 am
If it's okay for webUser to select from any user table in the database, you can make him a member of db_datareader fixed database role. Can you check the old database to see if that's how it's set up?
Greg
March 30, 2011 at 10:28 am
I'd put the archive tables in their own schema and grant webuser select rights on the schema, then webuser will have select permissions on any objects created in that schema without having to do anything extra. Just be sure you are only putting objects you want webuser to be able to read in that schema.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 30, 2011 at 11:04 am
i've played with a database trigger that adds permissions to a role when a DDL event like creating a table occurs;
this would also do what you are asking:
CREATE TRIGGER [TR_AddNewObjects_ToDefaultRoles]
ON DATABASE
FOR
CREATE_PROCEDURE,
CREATE_FUNCTION,
CREATE_VIEW,
CREATE_TABLE
AS
BEGIN
EXECUTE AS LOGIN = 'superman' --this user has rights to fiddle with creating/adding roles
SET NOCOUNT ON
declare @ObjectDef table(definition varchar(max))
declare
@eventData XML,
@DATABASENAME SYSNAME,
@EVENTDATE DATETIME,
@USERNAME SYSNAME,
@SYSTEMUSER VARCHAR(128),
@CURRENTUSER VARCHAR(128),
@ORIGINALUSER VARCHAR(128),
@HOSTNAME VARCHAR(128),
@APPLICATIONNAME VARCHAR(128),
@SCHEMANAME SYSNAME,
@OBJECTNAME SYSNAME,
@OBJECTTYPE SYSNAME,
@EVENTTYPE VARCHAR(128),
@COMMANDTEXT VARCHAR(max),
@NAMEFORDEFINITION VARCHAR(261),
@CMD VARCHAR(500)
--Load Variables from the xml
SET @eventData = eventdata()
SELECT
@DATABASENAME = db_name(),
@EVENTDATE = GETDATE(),
@USERNAME = @eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
@SYSTEMUSER = SUSER_SNAME(),
@CURRENTUSER = CURRENT_USER,
@ORIGINALUSER = ORIGINAL_LOGIN(),
@HOSTNAME = HOST_NAME(),
@APPLICATIONNAME = APP_NAME(),
@SCHEMANAME = @eventData.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME'),
@OBJECTNAME = @eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
@OBJECTTYPE = @eventData.value('data(/EVENT_INSTANCE/ObjectType)[1]', 'SYSNAME'),
@COMMANDTEXT = @eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(MAX)'),
@EVENTTYPE = @eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)')
--get the object definition
SET @NAMEFORDEFINITION = '[' + @SCHEMANAME + '].[' + @OBJECTNAME + ']';
--select the right role for the event.
PRINT 'Event Type: ' + @EVENTTYPE
IF @EVENTTYPE = 'CREATE_TABLE'
BEGIN
SET @CMD = 'GRANT SELECT ON [Schema.TheObject] TO ROLE_ALLTABLES_SELECT'
SET @CMD = REPLACE(@CMD,'[Schema.TheObject]',@NAMEFORDEFINITION)
PRINT @CMD
EXEC(@CMD)
END;
REVERT; --let go of the superman permissions
END --DB TRIGGER
GO
ENABLE TRIGGER [TR_AddNewObjects_ToDefaultRoles] ON DATABASE
Lowell
March 30, 2011 at 12:26 pm
Very well. Then. Thanks guys. I'm going to tool around w/ urr suggestions and get back to u.
thanks
March 30, 2011 at 1:04 pm
Alright, so it turns out the webUser was already tied to a webUser role. Are you guys saying if i ensure the webUser role has access to these new tables then the webuser itself will also have access to sed new tables? If so then that will make it much easier because i'm dealing w/ about 100 db's each w/ their own webUser_db permissions. I assume in these instances it is better to have them all tied to a common role for this exact scenerio.....So when u need to modify a user u dont have to change it everywhere.
Does that sound logical?
March 30, 2011 at 1:10 pm
In my opinion using roles is the best way to assign permissions. Grant permissions to the role and assign users to the appropriate role(s). I still think in the scenario you describe with archive table being created by an automated process, that using a dedicated schema for those automatically created tables and granting select on the schema is the best way to manage permissions.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 30, 2011 at 1:27 pm
if i were to take the schema route the uses would automatically have the proper rights to those newely created tables correct? Assumeing i set up the users permission to the schema properly.
March 30, 2011 at 1:35 pm
Yes, if you go the schema route and grant the necessary permissions on the schema then the rights are "inherited" for every object in the schema. Here's an example:
User: WebUser - no explicit permissions granted
Role: WebUsers - WebUser is added to this role.
Schema: WebArchive - WebUsers granted SELECT permissions
Tables: WebArchive.SalesJanuary2011
WebArchive.SalesFebruary2011
dbo.SalesMarch2011
With this simple setup WebUser would have SELECT persmissions on the first 2 tables, but not the third.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply