The DBA Blog by Tomer Lev

SQL information, scripts, tricks and more…

The old argument between Programmer and DBA

Within my work experience, there is the “old argument” between DBA and programmer. The programmer does not always understands why the DBA would not give him

permissions for executions or DML operations in the DB (INSERT, UPDATE and DELETE) .

The DBA, as a data protector, tries to do his best in protecting the database, and the programmer from the other hand wants

an access to the db, as he wants to develop new stuf and writing some T_SQL with no help from the DBA. I have to say, that although the DBA does and really wants to help the

programmer, main part of his job is to protect the data and manage permissions.

I would say, that the best solution is to give the programmer a way to work in front of the DB, but on the other hand not to give the programmer a SYSADMIN role ūüôā

June 22, 2009 Posted by | General | , , , , | Leave a comment

SAVE XML EXCEUTION PLAN IN GRAPHIC WAY

Some DBA’s need to save the execution plan in graphic way. In order to do so you need to:

1.save your exceution query XML as filename.SQLPLAN

2. open the file with management studio, which open it in a graphic way instead of XML.

June 13, 2009 Posted by | SQL SERVER, Tips | , , , | Leave a comment

Grant Permissions script

/*this script will give you execution statements for all objects under your DB. Simply replace the DbAdmin with the user you want to give permissions to and that is it.*/

set nocount on

select ‘grant select, insert, update, delete on ‘ + name + ‘ to DbAdmin’ + char(13) + ‘go’

from     sysobjects

where¬†¬† type = ‘U’

and¬†¬†¬†¬†¬†¬† name not like ‘dtp%’

select ‘grant select, insert, update, delete on ‘ + name + ‘ to DbAdmin’ + char(13) + ‘go’

from     sysobjects

where¬†¬† type = ‘V’

select ‘grant exec on ‘ + name + ‘ to DbAdmin’ + char(13) + ‘go’

from     sysobjects

where¬†¬† type = ‘P’

and¬†¬†¬†¬†¬†¬† name not like ‘dt%’

select ‘grant exec on ‘ + name + ‘ to DbAdmin’ + char(13) + ‘go’

from     sysobjects

where¬†¬† type = ‘FN’

and¬†¬†¬†¬†¬†¬† name not like ‘dt%’

June 12, 2009 Posted by | Permissions | , , , | Leave a comment