SQL Server - Search SQL object used in all database

Some times we pass through situation where we need to search specific object ever used in all databases.


Example: Finding whether Employee table in Payroll database is used in Accounts database. If yes than which are views, procedures table has been  used.


Below is simple query which will browse through each database to search given Table




DECLARE @DBNAME NVARCHAR(100)
DECLARE dbCUR CURSOR FOR SELECT name FROM SYS.DATABASES
open dbcur


FETCH DBCUR INTO @DBNAME


WHILE @@FETCH_STATUS = 0
BEGIN

--find in views

DECLARE @sql nvarchar(500)
DECLARE @ParmDefinition nvarchar(500)
DECLARE @cnt int

SET @sql = N'SELECT @cntout = count(*) FROM [' + @dbname + '].INFORMATION_SCHEMA.VIEWS WHERE view_definition like ''%Employee%'''
SET @ParmDefinition = N'@cntout int OUTPUT';

EXECUTE sp_executesql @sql,@ParmDefinition,@cntout=@cnt output



if @cnt > 0 
begin
print convert(nvarchar(10),@cnt) + ' views in ' + @dbname
end

--find procedure

SET @sql = N'SELECT @cntout = count(*) FROM [' + @dbname + '].sys.sql_modules modu
INNER JOIN [' + @dbname + '].sys.objects obj ON modu.object_id = obj.object_id
WHERE obj.type = ''P'' AND modu.definition like ''%%Employee%'''


SET @ParmDefinition = N'@cntout int OUTPUT';
EXECUTE sp_executesql @sql,@ParmDefinition,@cntout=@cnt output



if @cnt > 0 
begin
print convert(nvarchar(10),@cnt) + ' procedures in ' + @dbname
end

FETCH DBCUR INTO @DBNAME
END


close dbcur
deallocate dbcur

Hope it helps.

Redmine - Bulk Import for LDAP users

If you configure LDAP for authentication, users are not visible unless they have at least logged in once. This is not ideal in terms of usability. You have to tell your users to login but they can't do anything because there are not member of any project (yet) and you can't add them because they don't exist (chicken and egg).


To solve this we created a PHP script to implement Bulk Import in Redmine database.

/* ----------------- PHP script ------------------- */





//Postgres Variables

$pg_sqldb_host = "pghost";
$pg_sqldb_user = "pguser";
$pg_sqldb_pass = "pgpass";
$pg_sqldb_name = "redmine";


// LDAP variables
$ldaphost = "ldap.server.com";  // your ldap servers
$ldapport = 389;  // your ldap server's port number


// Connecting to LDAP
$ldapconn = ldap_connect($ldaphost, $ldapport) or die("Could not connect to $ldaphost");


ldap_set_option($ldapconn, LDAP_OPT_PROTOCOL_VERSION, 3);
ldap_set_option($ldapconn, LDAP_OPT_REFERRALS, 0);


$login = ldap_bind( $ldapconn, "ldapuser", "ldappass" );


$attributes = array("uid", "displayname", "mail");
$filter = "(uid=*)";


$result = ldap_search($ldapconn, $search_base, $filter, $attributes) or die("error in query") ;


$data = ldap_get_entries($ldapconn, $result);


$redmine_link = pg_connect("host=$pg_sqldb_host port=5432 dbname=$pg_sqldb_name user=$pg_sqldb_user password=$pg_sqldb_pass") or die("Could not connect to Redmine: " . pg_last_error());




$query = "select MAX(id)+1 from users";
$result = pg_query($redmine_link, $query);
$nextid = pg_fetch_result($result, 0, 0);


for ($i=0; $i<=$data["count"];$i++) {


$query = "select id from users where login='" . $data[$i]["uid"][0] . "'";
$result = pg_query($redmine_link, $query);


$rows = pg_num_rows($result);

if($rows == 0 )
{
$name = split(" " , $data[$i]["displayname"][0]);


$query = "INSERT INTO users (id, login, firstname, lastname, mail,        mail_notification, admin, status, language, auth_source_id, created_on, type)
VALUES($nextid,'" . $data[$i]["uid"][0] . "','" . $name[0] . "','" . $name[1] . "','" . $data[$i]["mail"][0] . "',false,false,1,'en',$auth_source_id,CURRENT_TIMESTAMP,'User')";


$result = pg_query($redmine_link, $query);
$rows = pg_affected_rows($result);


//user doesn't exists add user
echo ("User " . $data[$i]["uid"][0] . " added successfully
");
$nextid++;
}
else
{
echo ("User " . $data[$i]["uid"][0] . " already exists
");
}
}


ldap_unbind($login);
ldap_close($ldapconn);


print("Users imported successfully
");


?>

/* ----------------- end of script ------------------- */

You may add script in schedule to run every 24 hours.

Linux : add php script in crontab. 

0 0 * * * *  root /user/bin/php /path/phpscript >> /var/log/logfile

Windows: add php script in windows scheduler.

Comments are most welcome.

Official website of Shrinathji Temple, Nathdwara

Shrinathji Temple, Nathdwara has launched official website. Website provides latest information about Shrinathji Temple, along with online services like Donation, Darshan Timings, News, Events, Cottage Booking for New Cottage and Dhirajdham.

www.nathdwaratemple.org

Subversion + Redmine + LDAP automatically generate AuthzSVNAccessFile

Subversion + Redmine + LDAP automatically generate AuthzSVNAccessFile

While implementing Redmine we came across difficulty integrating Redmine and Subversion using LDAP. One of the main reason was subversion uses AuthzSVNAccess file to authorize users. We don’t want all LDAP users to access SVN, only users added in respective Redmine project should able to access SVN repositories.

To solve this we end up creating PHP scripts to auto generate AuthzSVNAccessFile based on Redmine database. Script can be added to crontab to run periodically. Below are detail steps and script.

1) Install Redmine

2) Install Subversion

3) Automatically create SVN repository when a new project is added in Redmine

Add this script in crontab on Subvserion server (in case your subversion is hosted on different server). Below script is scheduled to run every 10 minutes.

10 * * * * root ruby /root/redmine-1.0.0/extra/svn/reposman.rb --redmine-host http://my.redmine./ --svn-dir /data/svn/ --url my.svn.server --key=mykey --owner apache --verbose >> /var/log/reposman.log

4) Configure Apache to use LDAP for subversion

ErrorDocument 404 default

DAV svn

Require valid-user

SVNParentPath /data/svn/

SVNListParentPath off

#LDAP configuration

AuthType Basic

AuthName "USVN"

AuthBasicProvider "ldap"

AuthLDAPURL "ldap://ldapserver"

authzldapauthoritative on

AuthLDAPBindDN "DN"

AuthLDAPBindPassword "LDAP Password"

Require valid-user

# Path to AuthzSVNAccessFile which will be auto generated using script

AuthzSVNAccessFile /etc/svn-acl-conf

5) PHP script to automatically generate AuthzSVNAccessFile

$authfile = "/etc/svn-acl-conf"; //path of AuthzSVNAccessFile

//redmine database detail. I have used Postgres.

$pg_sqldb_host = "localhost";

$pg_sqldb_user = "redmine";

$pg_sqldb_pass = "redmine";

$pg_sqldb_name = "redmine";

$fp = fopen($authfile, "w");

fwrite($fp, "# Subversion authorization file created by script \n");

$redmine_link = pg_connect("host=$pg_sqldb_host port=5432 dbname=$pg_sqldb_name user=$pg_sqldb_user password=$pg_sqldb_pass") or die("Could not connect to Redmine: " . pg_last_error());

#section [groups]

fwrite($fp, "[groups]\n");

#create groups from redmine projects

$query = "select id,name,identifier from projects";

$result = pg_query($redmine_link, $query);

while ($row = pg_fetch_array($result,NULL,PGSQL_ASSOC)) {

$group_name = $row["identifier"];

$group_id = $row["id"];

$group_users = NULL;

//get users for this group

$query = "select b.id, b.login from members a, users b where a.user_id=b.id AND a.project_id=$group_id";

$usvn_users_result = pg_query($redmine_link, $query);

while ($userrow = pg_fetch_array($usvn_users_result,NULL,PGSQL_ASSOC)) {

$group_users = $group_users . ($group_users != NULL ? ", " : "") . $userrow["login"];

}

pg_free_result($usvn_users_result);

fwrite($fp, "$group_name = $group_users \n");

}

pg_free_result($result);

#section [project]

fwrite($fp, "\n\n#Projects from Redmine \n");

$query = "select id,name,identifier from projects";

$result = pg_query($redmine_link, $query);

while ($row = pg_fetch_array($result,NULL,PGSQL_ASSOC)) {

$project_name = $row["name"];

$project_id = $row["id"];

$project_identifier = $row["identifier"];

fwrite($fp, "#Project $project_name \n");

fwrite($fp, "[$project_identifier:/] \n");

fwrite($fp, "@$project_identifier = rw \n\n");

}

pg_free_result($result);

pg_close($redmine_link);

fclose($fp);

print("SVN Auth file $authfile generated sucessfully");

?>

//End of code

- Make sure apache user has write access to AuthzSVNAccessFile- Add PHP script in crontab to run every 10 minutes10 * * * * /usr/bin/php /path/myscript.php