January 5, 2011 at 2:09 pm
I am looking for a tool which would give me the list of permissions it needs on sql server, what i mean is we are using a sql account from our webservers connecting to sql servers. If i provide set of tasks, is there any tool which i run against this set so that it can tell me the exact permission what those tasks need. Goal is to provide just the right amount of access the account needs. Any thoughts?
January 5, 2011 at 2:34 pm
Test Driven Development suggests running it and collecting the error messages, then fixing those.
Otherwise, figure out what the tool will be doing, and where it will be doing it, and assign the permissions that seem to make the most sense.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 5, 2011 at 2:50 pm
App is already live with sa priviledges., trying to cutdown what priviledge it exactly needs.
January 6, 2011 at 7:57 am
Run a trace to capture every command it issues to the database. From that, you'll be able to work out what tables, procs, etc., it is accessing. Then give it permissions on those.
Alternately, I usually just make sure an application has datareader, datawriter, and stored procedure execution rights, and leave that alone as good enough. There usually isn't reason to truly minimize it to just the tables needed.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 6, 2011 at 8:59 am
Yeah i can try that i think i might end up giving sa priviledge to the account since the app also kicks off few jobs unless i manually give permission to each job. Thanks
January 7, 2011 at 12:16 pm
sqldba_icon (1/6/2011)
Yeah i can try that i think i might end up giving sa priviledge to the account since the app also kicks off few jobs unless i manually give permission to each job. Thanks
I won't bother mentioning what could happen if one of your end users happens to exploit this SA account using SQL injection.
Ideally you would grant the account public membership with nothing but exec permission on stored procedrues that you write. At this point, if you want to go the easy route, then in addition to full db_datareader and db_datawriter on the specific database they need access to, you can grant them membership in one of the SQL Server Agent fixed roles. That will cover the need to start a job without being a member of SA.
http://msdn.microsoft.com/en-us/library/ms188283.aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 7, 2011 at 1:14 pm
Eric Russell 13013 (1/7/2011)
sqldba_icon (1/6/2011)
Yeah i can try that i think i might end up giving sa priviledge to the account since the app also kicks off few jobs unless i manually give permission to each job. ThanksI won't bother mentioning what could happen if one of your end users happens to exploit this SA account using SQL injection.
Ideally you would grant the account public membership with nothing but exec permission on stored procedrues that you write. At this point, if you want to go the easy route, then in addition to full db_datareader and db_datawriter on the specific database they need access to, you can grant them membership in one of the SQL Server Agent fixed roles. That will cover the need to start a job without being a member of SA.
Thanks. If i give only exec permissions on the procedures,lets say i am deploying 80 procs( which we do) then i will need to individually grant exec perm to each of these since there isnt a server or db role which will give exec to all the procs?
January 7, 2011 at 1:37 pm
sqldba_icon (1/7/2011)
Thanks. If i give only exec permissions on the procedures,lets say i am deploying 80 procs( which we do) then i will need to individually grant exec perm to each of these since there isnt a server or db role which will give exec to all the procs?
Yes, I reccomend role based security, where exec permission on all minimally required procedures calls are granted to the appropriate role. The scripts that I use to deploy stored procedures, I follow each "create procedure" statement with it's "grant exec" statements. So, re-granting permissions is not a manual step for the DBA whenever a stored procedure is deployed. If another role needs access to a procedure, then I'll check the script out of source control, add the grant exec, check in, then submit for deployment.
create procedure [Billing].[Reference_Reload]
as
...
..
GO
grant exec on [Billing].[Reference_Reload] to [BillingAppRole];
grant exec on [Billing].[Reference_Reload] to [BillingReportingRole];
GO
To make it easy, you can generate all of your grant exec statements by querying the INFORMATION_SCHEMA tables and transforming the output into T-SQL like so:
select 'grant exec on '+ROUTINE_SCHEMA+'.'+ROUTINE_NAME+' to [BillingAppRole];'
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_SCHEMA = 'Billing';
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply