June 17, 2010 at 11:19 am
I've created several stored procedures to better isolate users from data. When I tried to grant permissions to the stored procedure, the Permissions page of the SP's properties window does not have an 'Add' button. I did notice thta the Owner is 'dbo'.
Where do I go from here?
June 17, 2010 at 3:52 pm
You can easily use a T-SQL statement
GRANT EXEC on <sproc name>
TO <username>
Check the SQL Books Online, I sometimes get this one reversed.
If you have a large list of sprocs, query them out, and then add the other statements around them. I have even done that using an ASP.NET page to write out all the sql code for me. Then all I do is copy and paste.
Andrew SQLDBA
June 17, 2010 at 4:51 pm
Thx, I did figure out that I needed to grant execute permissions to the users. Right now, we only have 10-ish users so I've added them individually, but I'm now looking at a NT group and granting the group permissions include execute.
June 18, 2010 at 7:37 am
Another option that we use pretty frequently is to create a role within the database. Grant all permissions through the role, and then add users or AD groups to the role as needed. It makes security maintenance so much easier.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 18, 2010 at 8:50 am
If this is an application that is accessing the database, you would need only one user to grant permissions. You should not have to grant permissions to each user when using a database connection from the app or web site
Yes, roles are very handy for this, as well as an AD group.
Andrew SQLDBA
June 18, 2010 at 9:38 am
Its has an Access front end. When I was reading up on security yesterday, the articles that I read suggested that Application Roles required the password to be provided by the application and thus aren't preferred.
June 18, 2010 at 11:50 am
david.c.holley (6/18/2010)
Its has an Access front end. When I was reading up on security yesterday, the articles that I read suggested that Application Roles required the password to be provided by the application and thus aren't preferred.
Sorry, I wasn't clear. Create a database role. It's an excellent way to manage security.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply