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.
| Author |
Topic |
|
TxHemi57
Starting Member
2 Posts |
 Posted - 2007-01-16 : 11:56:56
|
| I have a database where I have created a specific SQL user that will be used to create tables, views, and stored procedures. I also have domain users that need to have select, execure, and modify permissions to all objects created by the SQL user. Originally I created a role and added the damain users to this role. Then I manually gave this role rights to the objects. The person creating the objects is contantly deleting existing objects and creating new ones. He recommends that I allow this role to inherit the rights of the db_datareader and db_datawriter and then deny access to all system objects. I am not sure I like this procedure.I would like a way to automacally allow this role to have rights to any object created by the SQL user/owner. Is there a way to do this?I have searched various forums and have found several ways like creating scripts but I need something that does this automatically.Thank you for any help you can provide. |
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2007-01-17 : 08:26:56
|
What the developer could do is to add a line to the bottom of his object scripts that grant the requisite permissions to the object for your role. That way when the object is recreated the permissions are granted. Here is an example for a stored procedure, but the other objects follow the same basic patternIF EXISTS(SELECT 1 FROM sysobjects WHERE name = 'myProc') DROP PROCEDURE dbo.myProcgoCreate Proc dbo.MyProc (@SomeParam int = 1 ) ASBEGIN print 'My Param IS ' + Convert (Varchar(10) , @SomeParam ) Return 0ENDGOIF EXISTS ( SELECT 1 FROM sysobjects WHERE name = 'MyProc') GRANT EXECUTE ON dbo.myProc TO MyRolego Personally I hate using Datareader datawriter roles, and think its just lazy programming on the developers part to use that, but then I'm a believer of the rule that ALL data access should be done through stored procedures. It's great fun watching them develop the stored procedures to handle ad hoc searching  -- RegardsTony The DBA |
 |
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2007-01-17 : 08:41:13
|
quote:
I have searched various forums and have found several ways like creating scripts but I need something that does this automatically.
Sorry I should have read your post more fully. using data Reader DataWriter will give you select insert update to the data tables and views for any objects created, for Stored procedures you must grant execute permission on the stored procedure. You could write a script that trawls sysobjects in the db, and grants the permissions on the objects and then schedule that to run on a regular basis, but what happens if the developer drops the object just after the script has run?I take it the 'developer' is not using scripts to develop the database but is doing it directly in Enterprise Manager . . . . Not the best idea, If you have a DB crash and restore you backup what objects were changed since the last backup? and what were those changes? And who had access to them? Script all DB objects (Using the generate Script from EM should be a last resort Yeuch!) using Query Analyser, if you need to apply the changes to another server You Can very easily . . . along with permissions to each objectRemember the mantra "The GUI is evil . . . ."-- RegardsTony The DBA |
|
|
|
TxHemi57
Starting Member
2 Posts |
Posted - 2007-01-17 : 11:21:40
|
| Thank you for the response. I will offer the stored procedure as a means to accomplish the goal. I am not sure if the developer is using GUI or script to create the db Objects. I do know that he has not been setting permissions himself. On the script the part "WHERE name = 'myProc'" I am guessing that this is going by the name of the stored procedure. Is there a way to do this based off of the owner and not the object name? |
|
|
|
|
|
|
|
|
User permissions to owner objects